SQL Server: Enable database service broker

Declare @MyDatabase Varchar(20)
Declare @MySQL Varchar(100)
declare MyCursor Cursor for SELECT RTRIM(name) DatabaseName
from master..sysdatabases
where status not in (512, 528)
and dbid > 4
Open MyCursor

Fetch next from MyCursor into @MyDatabase

While @@FETCH_STATUS >= 0
Begin
--Set @MySQL = 'ALTER DATABASE [' + @MyDatabase + '] SET ENABLE_BROKER WITH NO_WAIT' -- Enable Service Broker
Set @MySQL = 'ALTER DATABASE [' + @MyDatabase + '] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT' -- Change Page_Verify
--print (@MySQL)
exec(@MySQL)
Fetch next from MyCursor into @MyDatabase
End

Close MyCursor
Deallocate MyCursor

SQL Server: Query current database name

SELECT DB_NAME() AS DataBaseName

SQL Server: Query all database name

SELECT dbid, RTRIM(name) DatabaseName
From master..sysdatabases
Where status not in (512, 528)
and name not like 'temp%'
and dbid > 4

SQL Server: Query all table name with their schema

SELECT RTRIM(TABLE_SCHEMA) SchemaName, RTRIM(TABLE_NAME) TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

SQL Server: Query all orphaned user account in database

CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS
)

SET NOCOUNT ON

DECLARE @DBName sysname, @Qry nvarchar(4000)

SET @Qry = ''
SET @DBName = ''

WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
)

IF @DBName IS NULL BREAK

SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..syslogins sl
WHERE su.sid = sl.sid
)'

INSERT INTO #Results EXEC (@Qry)
END

SELECT * FROM #Results
where [Orphaned User] like '%rho%'
ORDER BY [Database Name], [Orphaned User]


drop TABLE #Results

SQL Server: Verify current Kerberos authenication status

--Authenicaiton mode
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
--Conneciton authenication mode
--select session_id,auth_scheme,net_transport,client_net_address from sys.dm_exec_connections

SQL Server: List all database schema

USE Master
GO

declare @MyTable as Varchar(10)
declare @sql as Varchar(500)

declare MyCursor cursor for
SELECT name
FROM sys.Databases
where name like 'PDL%'

Open MyCursor
Fetch next from MyCursor into @MyTable

while @@FETCH_STATUS = 0
Begin
set @sql = ' use ' + @MyTable +
' SELECT Table_catalog,table_schema,table_name,table_type FROM information_schema.Tables where TABLE_SCHEMA <> ''dbo'''
exec(@SQL)
Fetch next from MyCursor into @MyTable
End

close MyCursor
deallocate MyCursor