Making a more reliable and flexible sp_MSforeachdb

Making a more reliable and flexible sp_MSforeachdb

SQL Server Websites

SQL Server Websites

Sending email from SQL Server Integration Services (SSIS)

Sending email from SQL Server Integration Services (SSIS)

Using the Foreach ADO Enumerator in SSIS - CodeProject

Using the Foreach ADO Enumerator in SSIS - CodeProject

Tempdb Configuration Best Practices in SQL Server

Tempdb Configuration Best Practices in SQL Server

Remove SQL Server database from single-user mode

Remove SQL Server database from single-user mode

SQL Server Security Audit Report

USE master
GO
SET nocount ON

-- Get all roles
CREATE TABLE #temp_srvrole
(ServerRole VARCHAR(128), Description VARCHAR(128))
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole

-- sp_help syslogins
CREATE TABLE #temp_memberrole
(ServerRole VARCHAR(128),
MemberName VARCHAR(265),
MemberSID VARCHAR(300))

DECLARE @ServerRole VARCHAR(128)

DECLARE srv_role CURSOR FAST_FORWARD FOR
SELECT ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END

CLOSE srv_role
DEALLOCATE srv_role

SELECT ServerRole, MemberName FROM #temp_memberrole

-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS(SELECT *FROM #temp_memberrole
WHERE MemberName = 'BUILTIN\Administrators'
AND ServerRole = 'sysadmin' )
BEGIN
CREATE TABLE #temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'

SELECT output AS local_administrator
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin
END

DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole

-- Get individual Logins
SELECT name, 'Individual NT Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 1
UNION
SELECT name, 'Individual SQL Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 0
UNION ALL
-- Get Group logins
SELECT name,'NT Group Login' LoginType
FROM syslogins
WHERE isntgroup = 1


-- get group list
-- EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
CREATE TABLE #temp_groupadmin
(output VARCHAR(8000))
CREATE TABLE #temp_groupadmin2
(groupName VARCHAR(256), groupMember VARCHAR(1000))
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)

DECLARE grp_role CURSOR FAST_FORWARD FOR
SELECT REPLACE(name,'US\','')
FROM syslogins
WHERE isntgroup = 1 AND name LIKE 'US\%'

OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin

PRINT @sqlcmd
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd

SET ROWCOUNT 8
DELETE FROM #temp_groupadmin

SET ROWCOUNT 0

INSERT INTO #temp_groupadmin2
SELECT @grpname, output FROM #temp_groupadmin
WHERE output NOT LIKE ('%The command completed successfully%')

FETCH NEXT FROM grp_role INTO @grpname
END


CLOSE grp_role
DEALLOCATE grp_role

SELECT * FROM #temp_groupadmin2

DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2



PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins
PRINT ''


-- Get all the Database Rols for that specIFic members
CREATE TABLE #temp_rolemember
(DbRole VARCHAR(128),MemberName VARCHAR(128),MemberSID VARCHAR(1000))
CREATE TABLE #temp_rolemember_final
(DbName VARCHAR(100), DbRole VARCHAR(128),MemberName VARCHAR(128))

DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)

DECLARE grp_role CURSOR FOR
SELECT name FROM sysdatabases
WHERE name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'


OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

TRUNCATE TABLE #temp_rolemember
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'

PRINT @sqlcmd2
INSERT INTO #temp_rolemember
EXECUTE(@sqlcmd2)

INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName, DbRole, MemberName
FROM #temp_rolemember

FETCH NEXT FROM grp_role INTO @dbname
END


CLOSE grp_role
DEALLOCATE grp_role

SELECT * FROM #temp_rolemember_final

DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final

SSIS - Dynamically set Flat File Connection Manager - Sudeep's Domain

SSIS - Dynamically set Flat File Connection Manager - Sudeep's Domain

SQL Server Encryption - Encrypt a database

USE master;
GO
--master key password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '****';
--DROP MASTER KEY
go
--database encryption key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Certificate'
--DROP CERTIFICATE MyServerCert
go


USE master
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\File.cer'
WITH PRIVATE KEY (FILE = 'C:\Key.pvk' ,
ENCRYPTION BY PASSWORD = '*****' )
GO


USE [cleint]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE [cleint]
SET ENCRYPTION ON
GO

SELECT db_name(database_id), encryption_state, *
FROM sys.dm_database_encryption_keys with (nolock)


SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys

SQL 2008 Server Audit - Server and Database Specification

--Create Audit
CREATE SERVER AUDIT [DDL-Audit]
TO FILE
( FILEPATH = N'E:\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '877f038b-d57c-44d1-8db4-c2c3dc1321a1'
)
GO

--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO

--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout]
FOR SERVER AUDIT [Login-Logout-Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO


--Create Audit Specific Database Level
CREATE DATABASE AUDIT SPECIFICATION [DDL_changes_audit]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

--Read from Audit Trace File
select * FROM sys.fn_get_audit_file('E:\Audit\DDL*',default,default)


--Disable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = OFF)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = OFF)


--Enable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = ON)

Jobs on Server and detail information

IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]

--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
--where x.running = 1

SQL Server Integration Services (SSIS) Performance Best Practices

http://www.mssqltips.com/tip.asp?tip=1840

http://www.mssqltips.com/tip.asp?tip=1867

Microsoft SQL Server vs. MySQL

Microsoft SQL Server vs. MySQL

Apply a service pack or hotfix SQL Server 2008 to a failover cluster instance

Getting a SQL Server RowCount Without doing a Table Scan

SELECT [name]
, dbo.[fn_get_table_rowcount] ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [Row Count] desc
GO


----------------------------------------------------------------------
--FUNCIOTN
----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_table_rowcount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_table_rowcount]
GO


CREATE FUNCTION dbo.[fn_get_table_rowcount]
(
@sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
AS BEGIN

DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID int -- Object ID

SET @nObjectID = OBJECT_ID(@sTableName)

-- Object might not be found
IF @nObjectID is null RETURN NULL

SELECT TOP 1 @nRowCount = rows
FROM sysindexes
WHERE id = @nObjectID AND indid < 2

RETURN @nRowCount
END
GO

SQL 2008 Data Collection

Data Collection


Manually enable data collection
Managing Data Collection Using Transact-SQL

SQL Server 2008 Data Collector – Video Tutorial

Data Collector: Remove Data Collector to Remove Associated Objects

SQL server CPU Allocation and usage

--general sql configuration
EXEC sys.sp_configure

--server available CPU
select cpu_count from sys.dm_os_sys_info

--server used cpu by sql
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'

--server physical memory
select physical_memory_in_bytes from sys.dm_os_sys_info

UPDATE STATISTICS

sp_MSforeachtable'UPDATE STATISTICS ?;'

Custom Reports Library - SQL Server

SQL Server Manageability Team Blog
http://blogs.msdn.com/b/sqlrem/

http://blogs.msdn.com/b/sqlrem/archive/2010/06/06/mdw-overview-report-for-data-collector-mdw-report-series-part-1.aspx

http://blogs.msdn.com/b/sqlrem/archive/2010/06/19/mdw-overview-report-for-data-collector-server-activity-mdw-report-series-part-2.aspx

http://blogs.msdn.com/b/sqlrem/archive/2010/06/21/server-activity-history-report-mdw-report-series-part-3.aspx

http://blogs.msdn.com/b/sqlrem/archive/2010/06/28/server-activity-history-report-mdw-report-series-part-4.aspx

sqlserverpedia.com - powered by Quest

sp_who2 - in detail analysis with temp table

drop table #tmp

create table #tmp
(SPID int,
Status nvarchar(100),
Login nvarchar(100),
HostName nvarchar(100),
BlkBy nvarchar(100),
DBName nvarchar(100),
Command nvarchar(100),
CPUTime int,
DiskIO int,
LastBatch nvarchar(100),
ProgramName nvarchar(100),
SPID2 int,
REQUESTID int)

insert into #tmp
exec sp_who2 active

select * from #tmp order by DiskIO desc
select * from #tmp where Login !='sa' order by LastBatch asc
select * from #tmp where Status !='sleeping' and Login !='sa'

select * from #tmp where DBName = 'ces_flextronics'
--dbcc inputbuffer(492)

Finding the Lead Blocker in a Set of Blocking Process Reports

Script 1:
---------------------------------
SELECT sc.session_id AS UserSPID,
sr.blocking_session_id AS BlockingSPID,
DB_NAME(st.dbid) AS DatabaseNm,
ss.program_name AS AccessingProgram,
ss.login_name AS LoginNm,
OBJECT_NAME(st.objectid, st.dbid) AS ObjectNm,
CAST(st.text AS VARCHAR(MAX)) AS RequestSQL
--INTO #Processes
FROM sys.dm_exec_sessions ss
JOIN sys.dm_exec_connections sc ON ss.session_ID = sc.session_ID
LEFT JOIN sys.dm_exec_requests sr ON sc.connection_ID = sr.connection_ID
CROSS APPLY sys.dm_exec_sql_text (sc.most_recent_sql_handle) AS st
WHERE sc.session_id > 50 --gets us user processes
and isnull(sr.blocking_session_id,0) >0


Script 2:
---------------------------------

/*
--************************************************************************************
-- Description:    This script list the current locking block chain.
--            It will create 1 function: fn_GetLockChainSubTree
--                     1 table: LockChain
--
--
-- Parameters: NONE
--
-- Returns: Locking block chain
--
-- Created:     9/07/2008    John Liu
--
-- Typical Usage:    This script is typically used to find the block chain information
--
-- Comments: It also lists the dead lock (if any) at the end.
--      This script is for SQL 2005 and SQL 2008
--
--
-- Modifications:
--
-- Initials Date Description
-- -------- ---------- -----------------------------------------------------------
--
--************************************************************************************
*/

--***************************************Begin of create functions ********************************
--***************************************** Begin of dbo.fn_GetLockChainSubTree ************************
IF OBJECT_ID('dbo.fn_GetLockChainSubTree') IS NOT NULL DROP FUNCTION dbo.fn_GetLockChainSubTree
go

CREATE FUNCTION dbo.fn_GetLockChainSubTree(@Blocker AS INT)
RETURNS @TREE TABLE
(
Blocker INT NOT NULL,
Blocked INT NOT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Block_Subtree(Blocker,Blocked, lvl)
AS
(
-- Anchor Member (AM)
SELECT
Blocker
,Blocked
,0
FROM
LockChain
WHERE
Blocker = @Blocker

UNION all

-- Recursive Member (RM)
SELECT
l.Blocker
, l.Blocked
, t.lvl+1
FROM
LockChain AS l
INNER JOIN
Block_Subtree AS t
ON
l.Blocker = t.Blocked
)

INSERT INTO
@TREE
SELECT
*
FROM
Block_Subtree

RETURN
END
GO
--***************************************** End of dbo.fn_GetLockChainSubTree ************************
--***************************************End of create functions ********************************

--*************************************************************************************
--**********************************         Main section         **************************
--*************************************************************************************
-- -----------------------------------------------
-- Core processing
-- -----------------------------------------------
IF OBJECT_ID('LockChain') IS NOT NULL DROP TABLE LockChain
go

--initial the LockChain table
SELECT
Blocker = Blocking_Session_ID
,Blocked = Session_ID
INTO
LockChain
FROM
sys.dm_exec_requests
WHERE
Blocking_Session_ID <> 0

--list the locking chain
SELECT
t.*,s.host_name,s.program_name,s.login_name,s.nt_user_name
FROM
LockChain l
CROSS APPLY
fn_GetLockChainSubTree(l.Blocker) as t
INNER JOIN
sys.dm_exec_sessions s
ON
l.blocker = s.session_id
WHERE
l.blocker NOT IN (
SELECT
blocked
FROM
LockChain
)

--report any dead lock
SELECT
'Dead Lock' = 'Dead Lock'
,*
FROM
LockChain
WHERE
blocker IN (
SELECT DISTINCT
blocked
FROM
LockChain
)
AND
blocked IN (
SELECT DISTINCT
blocker
FROM
LockChain
)

--clean up    
--DROP TABLE LockChain
--DROP FUNCTION dbo.fn_GetLockChainSubTree

-- -----------------------------------------------
-- End of Core processing
-- -----------------------------------------------
--***************************************** End of Main section **********************************

SQL Server Performance Blog - Idera

Full Text Search

--Create FTS
Use ClientDB
GO
EXEC sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG [FTC_LO_TEXT] WITH ACCENT_SENSITIVITY = OFF
GO
CREATE FULLTEXT INDEX ON [dbo].[lo_text] KEY INDEX [PK_lo_text] ON ([FTC_LO_TEXT]) WITH CHANGE_TRACKING AUTO)
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([descr])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([keywords])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([title])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ENABLE
GO

--Drop FTS
EXEC sp_fulltext_database 'disable'
GO
DROP FULLTEXT INDEX ON lo_text
GO
IF EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'FTC_LO_TEXT')
DROP FULLTEXT CATALOG [FTC_LO_TEXT]''
GO

--Disable Stopwords and Stoplists on FTS
ALTER FULLTEXT INDEX ON lo_text
SET STOPLIST OFF;
GO

EXEC sp_fulltext_catalog 'FTC_LO_TEXT', 'rebuild'
GO

--Rebuild FTS Catalog
ALTER FULLTEXT CATALOG FTC_LO_TEXT REBUILD


--Sample of FTS search
declare @str_decr nvarchar(50), @str nvarchar(50) , @str1 nvarchar(50) , @str2 nvarchar(50)
set @str1 = 'New'
set @str2 = 'Document'
set @str_decr = '%'+@str1+'%'

select * From lo_text lt where lt.title LIKE @str_decr-- or lt.descr LIKE @str_decr

set @str = @str1 + ' OR ' +@str2
select * from lo_text WHERE CONTAINS(title, @str)

set @str = @str1 + ' AND ' +@str2
select * from lo_text WHERE CONTAINS(title, @str)

set @str = @str1 + ' ' +@str2
select * from lo_text WHERE FREETEXT(*, @str)

set @str = '"'+@str1 + ' ' +@str2+'*"'
select * from lo_text WHERE FREETEXT(descr, @str)


NOTES:
1)If you cannot start the fdhost, please check the permission of FDLauncher Service logon account. Do you specify a user account for FDLauncher service during setup? If you didn't specify user account for FDLauncher Service, by default, FDLauncher is configured using the Local Service account. This account is not added to the SQLServerFDHostUser$$MSSQLSERVER group. To work around the issue, you need to assign Local Service account (or logon account of FDLauncher Service) to SQLServerFDHostUser$$MSSQLSERVER group. Then restart fdhost using sp_fulltext_service 'restart_all_fdhosts' procedure.

EXEC sp_fulltext_service 'restart_all_fdhosts'

Restore db from backup file with dynamic build

-----------------------------------------------------------------------
--RESTORE DB
-----------------------------------------------------------------------
use master
go

declare @backupfile nvarchar(200) , @sql nvarchar(1000), @restorepath nvarchar(100), @restoredbname nvarchar(100) ,@logicalname nvarchar(100), @ttype nvarchar(10)
set @backupfile = 'e:\ces_uws_backup_2010_09_13_182228_1716321.bak'
set @restorepath= 'E:\DATA\'

if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp

create table #tmp
(LogicalName nvarchar(100),
PhysicalName nvarchar(500),
Type nvarchar(2),
FileGroupName nvarchar(20),
Size bigint ,
MaxSize bigint ,
FileId int ,
CreateLSN int,
DropLSN int,
UniqueId uniqueidentifier,
ReadOnlyLSN int,
ReadWriteLSN int,
BackupSizeInBytes bigint,
SourceBlockSize bigint,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN nvarchar(100),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly int,
IsPresent int,
TDEThumbprint nvarchar(100))


set @sql = 'RESTORE FILELISTONLY from disk = '''+@backupfile+''''
print @sql
insert into #tmp
exec sp_executesql @sql

select * from #tmp


if(OBJECT_ID('tempdb..#tmp1')>1)
drop table #tmp1

create table #tmp1
(BackupName nvarchar(500),
BackupDescription nvarchar(500),
BackupType int,
ExpirationDate datetime,
Compressed int,
Position int ,
DeviceType int,
UserName nvarchar(100),
ServerName nvarchar(100),
DatabaseName nvarchar(100),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize bigint,
FirstLSN nvarchar(100),
LastLSN nvarchar(100),
CheckpointLSN nvarchar(100),
DatabaseBackupLSN nvarchar(100),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder int,
CodePage int,
UnicodeLocaleId int ,
UnicodeComparisonStyle int,
CompatibilityLevel int,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int ,
SoftwareVersionBuild int ,
MachineName nvarchar(100),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(100),
FamilyGUID uniqueidentifier,
HasBulkLoggedData datetime,
IsSnapshot int,
IsReadOnly int ,
IsSingleUser int,
HasBackupChecksums int,
IsDamaged int,
BeginsLogChain int,
HasIncompleteMetaData int,
IsForceOffline int,
IsCopyOnly int,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN nvarchar(100),
RecoveryModel nvarchar(100),
DifferentialBaseLSN nvarchar(100),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(500),
BackupSetGUID uniqueidentifier,
CompressedBackupSize bigint )


set @sql = 'restore headeronly from disk = '''+@backupfile+''' with nounload'
print @sql
insert into #tmp1
exec sp_executesql @sql

select * from #tmp1

select @restoredbname = replace(DatabaseName,'ces_','')+'_'+convert(nvarchar(4),year(BackupFinishDate))+'_'+convert(nvarchar(4),MONTH(BackupFinishDate))+'_'+convert(nvarchar(4),DAY(BackupFinishDate))
from #tmp1

select @restoredbname

set @sql = 'RESTORE DATABASE '+@restoredbname+' from disk = '''+@backupfile+'''
WITH FILE = 1, '


declare tmp_cur cursor for
select logicalname, type from #tmp order by fileid

open tmp_cur
Fetch next from tmp_cur into @logicalname,@ttype

while @@fetch_status = 0
begin
--print @logicalname
--print @ttype
set @sql = @sql + 'MOVE '''+@logicalname+''' TO '''+@restorepath+@restoredbname+(case when @ttype = 'D' then '.mdf' when @ttype = 'L' then '.ldf' else '.ndf' end )+''','
Fetch next from tmp_cur into @logicalname, @ttype
--print '---'
end

close tmp_cur
deallocate tmp_cur

set @sql = @sql + ' NOUNLOAD, REPLACE, STATS = 10'
print @sql
exec sp_executesql @sql

set @sql = 'ALTER DATABASE '+@restoredbname+' SET RECOVERY SIMPLE'
print @sql
exec sp_executesql @sql

set @sql = 'USE '+@restoredbname+'
DBCC SHRINKFILE (2,0)
'
print @sql
exec sp_executesql @sql

set @sql = 'update '+@restoredbname+'.dbo.corp_setting set [value] = ''deadbox@cyberu.com'' where [key] = ''OverrideEmailAddress'''
print @sql
exec sp_executesql @sql

View Database Backup History

---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date


-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database
-------------------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name

-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database - Detailed
-------------------------------------------------------------------------------------------
SELECT
A.[Server],
A.database_name,
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A

LEFT JOIN

(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name

Free Space on Drives with T-SQL

EXEC master.sys.xp_fixeddrives

Presentation Links: SQL Server Performance Tuning (Quest)

http://blogs.msdn.com/b/buckwoody/archive/2010/07/21/presentation-links-sql-server-performance-tuning-quest.aspx

Grant create table and SP for schema

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'CSODMGMT\SQLREWR')
CREATE USER [CSODMGMT\SQLREWR] FOR LOGIN [CSODMGMT\SQLREWR]
GO
EXEC sp_addrolemember N'db_datareader', N'CSODMGMT\SQLREWR'
GO
EXEC sp_addrolemember N'db_datawriter', N'CSODMGMT\SQLREWR'
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SQLREWR')
DROP SCHEMA [SQLREWR]
GO
CREATE SCHEMA [SQLREWR] AUTHORIZATION [CSODMGMT\SQLREWR]
GO
GRANT CREATE TABLE TO [CSODMGMT\SQLREWR]
go
GRANT CREATE PROCEDURE TO [CSODMGMT\SQLREWR]
go


----------------------------------
declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'CSODMGMT\SQLREWR'

DECLARE Cur_name CURSOR FOR
select specific_name From information_Schema.routines

OPEN Cur_name
FETCH NEXT FROM Cur_name INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
print '================================='
print @name
print '================================='
set @string ='GRANT VIEW DEFINITION ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string

set @string ='GRANT EXECUTE ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string



FETCH NEXT FROM Cur_name INTO @name
END
CLOSE Cur_name
DEALLOCATE Cur_name
GO

sp_msforeachdb

sp_msforeachdb
'
if ''?'' like ''ces_%''
begin
use [?]
print ''use [''+''?''+'']''
print ''GO''
print ''DROP FULLTEXT INDEX ON lo_text''
print ''go''
end
'

Find Currently Running Query

-- ALL Sessions
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

-- SPECIFIC SESSION DETAIL
dbcc inputbuffer(59)

Implementing the OUTPUT Clause in SQL Server 2008

http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/?utm_source=simpletalk&utm_medium=email-main&utm_content=OutputClause-20100726&utm_campaign=SQL

Batch File Commands

From the old days, sometimes batch file becomes very handy

http://academic.evergreen.edu/projects/biophysics/technotes/program/batch.htm

sp_MSforeachtable

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount order by rowcnt desc
select * from #rowcount
drop table #rowcount

Transparent Data Encryption in SQL Server 2008

http://www.sql-server-performance.com/articles/dba/Transparent_Data_Encryption_p1.aspx

http://www.mssqltips.com/tip.asp?tip=1507

http://msdn.microsoft.com/en-us/library/bb677274.aspx


You encounter SQL Server error 17308 after you enable TDE on one of your databases in SQL Server

Moving System Databases - A Checklist

Moving System Databases
Moving a database in SQL Server is normally as simple as detaching it from the server, moving it to it's new location and then attaching it again. However, that only counts for user databases. Moving system databases requires quite a bit more work. Since I had to do this a couple of times during the last weeks I wrote down a simple checklist that I could follow to make sure it was done right, so I did not screw up the servers and could speed up the process as much as possible. And, since I guess I will be doing it again sometime in the future and inevitably will lose the notes I scribbled down, I thought I could just as well post them here for safe-keeping. If anyone wants to use this checklist go ahead, but remember to do the steps in the exact order of the list, and make sure you have all the necessary backups before starting. To be completely clear, doing this wrong can completely screw up your databases and I can not take any responsibility if anything does go wrong. Also note that the checklist was written for the specific situation I was encountered with. Your system databases might have more data files and/or other file paths and names than those in the list, so you might need to make some changes. Have fun!

Make sure you have backups of all user databases plus master, model and msdb.
Moving msdb and model
In Enterprise Manager, right-click the server and choose Properties.
Click Startup Parameters.
Add a new parameter "-T3608" (without the quotes)
Stop SQL Server.
Start SQL Server, and make sure that SQL Agent is NOT started.
Run the following command in Query Analyzer:
-------------
use master
go
exec sp_detach_db 'msdb'
go
exec sp_detach_db 'model'
go
-------------

Move the data and log files for both msdb (normally msdbdata.mdf and msdblog.ldf) and model (normally model.mdf and modellog.mdf) to their new locations.
Run the following in Query Analyzer:
-------------
use master
go
exec sp_attach_db 'model'
, 'PATH_TO_MODEL_DATAFILE\model.mdf'
, 'PATH_TO_MODEL_LOGFILE\modellog.ldf'
go
-------------

Remove the -T3608 flag in Enterprise Manager/Server/Properties/Startup Parameters.
Stop SQL Server.
Start SQL Server.
Run the following in Query Analyzer and check that the file paths are correct:
-------------
use model
go
exec sp_helpfile
go
-------------

Run the following in Query Analyzer:
-------------
use master
go
exec sp_attach_db 'msdb'
, 'PATH_TO_MSDB_DATAFILE\msdbdata.mdf'
, 'PATH_TO_MSDB_LOGFILE\msdblog.ldf'
go
-------------

Run the following in Query Analyzer and check that the file paths are correct:
-------------
use msdb
go
exec sp_helpfile
go
-------------

Finished!
Moving tempdb
Run the following in Query Analyzer:
-------------
use master
go
alter database tempdb modify file (name = tempdev
, filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\tempdb.mdf')
go
alter database tempdb modify file (name = templog
, filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\templog.ldf')
go
-------------

Stop SQL Server
Start SQL Server
Run the following in Query Analyzer and check that the file paths correspond to those stated in step 1:
-------------
use tempdb
go
exec sp_helpfile
go
-------------

Stop SQL Server.
Move the original data and log files for tempdb to some new location, or rename them, just so that you are sure that SQL Server can not be using them.
Start SQL Server.
Run the following in Query Analyzer and check that no error occurs:
-------------
use tempdb
go
create table test (a int)
insert into test (a) values (1)
select * from test
drop table test
go
-------------

Remove the original data and log files for tempdb.
Finished!

NOTE: Steps 5 through 8 are of course not really necessary, they are just included as a quick extra check to really make sure that nothing went wrong. Skip them if you wish.
Moving master

Note: In this scenario we are not only moving the master database, we are also moving all of the files that SQL Server uses in it's 'data location' (as specified when installing SQL Server). The situation I encountered was that SQL Server's data location was specified to be something like D:\ (though with the program files as normal on C:\Program Files\Microsoft SQL Server\), but now the entire D:\ drive needed to be removed, so we needed to move everything SQL Server had stored there plus all references to it to avoid problems in the future. If you are only moving the master database you only need to follow the applicable steps of course.
In Enterprise Manager, right-click the server and choose Properties.
Click Startup Parameters.
Remove all of the three parameters that are already there (if there are more, remove the three that correspond to the three below in step 4).
Add the three following parameters:
-dPATH_TO_NEW_LOCATION_OF_MASTER_MDFFILE\master.mdf
-ePATH_TO_NEW_LOCATION_OF_SQLAGENT_ERRORLOG\ERRORLOG
-lPATH_TO_NEW_LOCATION_OF_MASTER_LOGFILE\mastlog.ldf

In my case the values of these parameters where as follows:
-dE:\MSSQL\Data\master.mdf
-eE:\MSSQL\LOG\ERRORLOG
-lE:\MSSQL\Data\mastlog.ldf
Stop SQL Server.
Move the files as specified below:
OLD_PATH_TO_MASTER_MDFFILE\master.mdf --> NEW_PATH_TO_MASTER_MDFFILE\master.mdf
OLD_PATH_TO_MASTER_LOGFILE\Data\mastlog.ldf --> NEW_PATH_TO_MASTER_LOGFILE\mastlog.ldf
OLD_PATH_TO_SQL_DATA_LOCATION\BACKUP --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\JOBS --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\LOG --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\REPLDATA --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)

Make the following changes to the registry (using regedit):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
BackupDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\BACKUP

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Replication
WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\REPLDATA

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
SQLDataRoot = NEW_PATH_TO_SQL_DATA_LOCATION\

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent
ErrorLogFile = NEW_PATH_TO_SQL_DATA_LOCATION\LOG\SQLAGENT.OUT
WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\JOBS

Note: This checklist does not cover servers using full-text search. If your server does use FT, then you will need to expand steps 6 and 7. Just move the FT directory in step 6 and search the registry for any references to it and change them as necessary.
Start SQL Server.
Finished!
If you are using SQL Agent on your server do not forget to check that it is running.
Conclusion
So there we are, all system databases moved. Again, please note that this was mainly meant as a checklist for myself, but feel free to use it as a base for your own checklist when you need to move system databases. I urge you to read through it several times so you are sure what it says and what you are doing. Most of the steps here come from the Microsoft article Moving SQL Server databases to a new location with Detach/Attach, which will probably help you more than this list. I simply compiled them into an easy-to-follow, step-by-step list that I could use to cut the down-time as much as possible.

By Christoffer Hedgate, 2004/11/30

Multi-statement Table Valued Function vs Inline Table Valued Function

remove orphan users

--create proc usp_remove_orphan_users as
set nocount on

-- Section 1: Create temporary table to hold databases to process

-- drop table if it already exists
if (select object_id('tempdb..#dbnames')) is not null
drop table #dbnames

-- Create table to hold databases to process
create table #dbnames (dbname varchar(128))

-- Section 2: Determine what databases have orphan users
exec master.dbo.sp_MSforeachdb 'insert into #dbnames select ''?'' from master..syslogins l right join ?..sysusers u
on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <> ''system_function_schema'')
having count(*) > 0'

-- Section 3: Create local variables needed
declare @CNT int
declare @name char(128)
declare @sid varbinary(85)
declare @cmd nchar(4000)
declare @c int
declare @hexnum char(100)
declare @db varchar(100)

-- Section 5: Process through each database and remove orphan users
select @cnt=count(*) from #DBNAMES
While @CNT > 0
begin

-- get the name of the top database
select top 1 @db=dbname from #DBNAMES

-- delete top database
delete from #DBNAMES where dbname = @db

-- Build and execute command to determine if DBO is not mapped to login
set @cmd = 'select @cnt = count(*) from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid' +
' where l.sid is null and u.name = ''DBO'''
exec sp_executesql @cmd,N'@cnt int out',@cnt out

-- if DB is not mapped to login that exists map DBO to SA
if @cnt = 1
begin
print 'exec ' + @db + '..sp_changedbowner ''SA'''
-- exec sp_changedbowner 'SA'
end -- if @cnt = 1


-- drop table if it already exists
if (select object_id('tempdb..#orphans')) is not null
drop table #orphans

-- Create table to hold orphan users
create table #orphans (orphan varchar(128))

-- Build and execute command to get list of all orphan users (Windows and SQL Server)
-- for current database being processed
set @cmd = 'insert into #orphans select u.name from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid ' +
'where l.sid is null and issqlrole <> 1 and isapprole <> 1 ' +
'and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' ' +
'and u.name <> ''system_function_schema'')'
exec (@cmd)


-- Are there orphans
select @cnt = count(*) from #orphans

WHILE @cnt > 0
BEGIN

-- get top orphan
select top 1 @name= orphan from #orphans

-- delete top orphan
delete from #orphans where orphan = @name

-- Build command to drop user from database.
set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
print @cmd
--exec (@cmd)


-- are there orphans left
select @cnt = count(*) from #orphans
end -- WHILE @cnt > 0


-- are the still databases to process
select @cnt=count(*) from #dbnames

end -- while @cnt > 0

-- Remove temporary tables
drop table #dbnames, #orphans

SQL Server DATE/TIME Workbench

Reviewing AutoGrow events from the default trace

DECLARE
@path VARCHAR(255),
@cmd VARCHAR(300);

-- customize this path, of course, if necessary:
SELECT
@path = 'C:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\',
@cmd = 'dir /b "' + @path + '*.trc"';

DECLARE @files TABLE
(
fn VARCHAR(64)
);

INSERT @files
EXEC master..xp_cmdshell @cmd;

DELETE @files
WHERE fn IS NULL;

SELECT
e.DatabaseName,
e.[FileName],
e.SPID,
e.Duration,
e.StartTime,
e.EndTime,
FileType = CASE e.EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END,
[TraceFile] = f.fn
FROM
@files f
CROSS APPLY
fn_trace_gettable(@path + f.fn, DEFAULT) e
WHERE
e.EventClass IN (92,93)
-- AND f.fn LIKE 'log[_][0-9]%.trc'
ORDER BY
e.StartTime DESC;

Excel - if a cell value exist in list on other column list

=IF(ISNA(VLOOKUP(A15,$E$1:$E$600,1,FALSE)),"",A15)

Cleanup duplicate record, another interesting method

SET ROWCOUNT 1
AGAIN:
DELETE member_mlc FROM
(SELECT memberid,mlc_status FROM member_mlc GROUP BY memberid,mlc_status HAVING COUNT(*) > 1) a
WHERE member_mlc.memberid = a.memberid and member_mlc.mlc_status = a.mlc_status
IF @@ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0

Wildcard search characters in SQL

underscore is a wildcard for single charachter

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '_a_a%' order by TABLE_NAME
ces_barclaysplc dbo data_type BASE TABLE
ces_barclaysplc dbo data_type_group BASE TABLE
ces_barclaysplc dbo data_type_group_operator BASE TABLE
ces_barclaysplc dbo datafeed_questionbank BASE TABLE
ces_barclaysplc dbo datafeed_questionbank_BAKUP BASE TABLE
ces_barclaysplc dbo datafeed_testanswer BASE TABLE
ces_barclaysplc dbo datafeed_testbank BASE TABLE
ces_barclaysplc dbo datafeed_testquestion BASE TABLE


to search for underscore use [_]

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '[___]%' order by TABLE_NAME
ces_barclaysplc dbo ___analytics_report_schedule_08_26_2009 BASE TABLE
ces_barclaysplc dbo ___datafeed_talent_20100210 BASE TABLE
ces_barclaysplc dbo ___datafeed_users_20100210 BASE TABLE
ces_barclaysplc dbo ___dataseg_ucf BASE TABLE
ces_barclaysplc dbo ___lo_options BASE TABLE
ces_barclaysplc dbo ___loadrl BASE TABLE
ces_barclaysplc dbo ___pdOnline_20100210 BASE TABLE

Show statistics and query plans

SET SHOWPLAN_ALL on
go
set statistics IO on
set statistics time on


.....QUERY IS HERE

GO

SET SHOWPLAN_ALL off
go

Resource Governor in SQL Server 2008 - Sample

http://www.sql-server-performance.com/articles/per/Resource_Governor_in_SQL_Server_2008_p1.aspx

VBScripts to manage Text Files

http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/other/textfiles/

Fixing SQL Cluster MSDTC

http://support.microsoft.com/kb/890634

filegroup get all DB tables and views info

-- Get all DB tables and views filegroup info
SET NOCOUNT ON

-- This table will hold the collected filegroup information
IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo

CREATE TABLE #tblFileGroupInfo(
TableOrViewName NVARCHAR(128),
[Table/View] VARCHAR(16),
AttributeName NVARCHAR(128),
AttributeType VARCHAR(128),
AttributeIndid INT,
FileGroupName NVARCHAR(128),
FileGroupID INT)


-- Get the file group information for all indexes of all tables and indexedviews
-- on the database. Note that auto-statistics created by SQL server are excluded,
-- since they are hardly of interest.
INSERT INTO #tblFileGroupInfo(
TableOrViewName,
[Table/View],
AttributeName,
AttributeType,
AttributeIndid,
FileGroupName,
FileGroupID)
SELECT a.TABLE_NAME,
CASE WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'
ELSE 'Table'
END,
ISNULL(b.[name], 'NA'),
CASE WHEN b.indid = 0 THEN 'Table Row Data (table has no
clustered index)'
WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'
WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
ELSE 'Non Clustered Index'
END,
b.indid,
FILEGROUP_NAME(b.groupid),
b.groupid
FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)
INNER JOIN sysindexes b WITH (NOLOCK)
ON OBJECT_ID(a.TABLE_NAME) = b.[id]
WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0


SET NOCOUNT OFF

SELECT *
FROM #tblFileGroupInfo WITH (NOLOCK)
ORDER BY TableOrViewName ASC, AttributeIndid ASC

-- Cleanup

IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo
GO


----------------------
--another simple way--
----------------------
-- to identofy which tables and indexes are used in which filegroup
select distinct(object_name(id)) from sysindexes where groupid=filegroup_id('Third')
select name from sysindexes where groupid=filegroup_id('Third') and indid > 0

Filegroup space usage and allocation

create table #Data(
FileID int NOT NULL,
[FileGroupId] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[FileName] sysname NOT NULL,
[FilePath] varchar(max) NOT NULL,
[FileGroup] varchar(MAX) NULL
)

create table #Results(
db sysname NULL ,
FileType varchar(4) NOT NULL,
[FileGroup] sysname not null,
[FileName] sysname NOT NULL,
TotalMB numeric(18,2) NOT NULL,
UsedMB numeric(18,2) NOT NULL,
PctUsed numeric(18,2) NULL,
FilePATH nvarchar(MAX) NULL,
FileID int null
)

create table #Log(
db sysname NOT NULL,
LogSize numeric(18,5) NOT NULL,
LogUsed numeric(18,5) NOT NULL,
Status int NOT NULL,
[FilePath] varchar(max) NULL
)

INSERT #Data (Fileid , [FileGroupId], TotalExtents ,
UsedExtents , [FileName] , [FilePath])
EXEC ('DBCC showfilestats')

update #Data
set #data.Filegroup = sysfilegroups.groupname
from #data, sysfilegroups
where #data.FilegroupId = sysfilegroups.groupid

INSERT INTO #Results (db ,[FileGroup], FileType , [FileName], TotalMB ,
UsedMB , PctUsed , FilePATH, FileID)
SELECT DB_NAME() db,
[FileGroup],
'Data' FileType,
[FileName],
TotalExtents * 64./1024. TotalMB,
UsedExtents *64./1024 UsedMB,
UsedExtents*100. /TotalExtents UsedPct,
[FilePath],
FileID
FROM #Data
order BY 1,2

insert #Log (db,LogSize,LogUsed,Status )
exec('dbcc sqlperf(logspace)')

insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, FileID)
select DB_NAME() db,
'Log' [FileGroup],
'Log' FileType,
s.[name] [FileName],
s.Size/128. as LogSize ,
FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
(s.Size/128. - FILEPROPERTY(s.name,'spaceused')/8.00 /16.00) UsedPct,
s.FileName FilePath,
s.FileId FileID
from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
where f.dbid = DB_ID()
and (s.status & 0x40) <> 0
and s.fileid=f.fileid
and l.db = DB_NAME()

SELECT r.*,
CASE WHEN s.maxsize = -1 THEN null
else CONVERT(decimal(18,2), s.maxsize /128.)
END MaxSizeMB,
CONVERT(decimal(18,2), s.growth /128.) GrowthMB
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.FileID = s.FileID
ORDER BY 1,2,3,4,5

DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log

SQL server performance trouble shooting tools

http://sqlserver-qa.net/blogs/tools/archive/2008/06/05/sql-server-troubleshooting-tools-pssdiag-sqldiag-sqlnexus-rml-utilities-and-readtrace-which-one-to-choose.aspx

Bunch of articles for SQL performace

http://www.simple-talk.com/sql/performance/

Finding the Causes of Poor Performance in SQL Server

Finding the Causes of Poor Performance in SQL Server, Part 1

Finding the Causes of Poor Performance in SQL Server, Part 2

Add "Command Prompt Here" Shortcut to Windows Explorer

Through the Registry

Navigate in your Registry to
HKEY_LOCAL_MACHINE/Software/Classes/Folder/Shell
and create a key called "Command Prompt" without the quotes.

Set the default string to whatever text you want to appear in the right-click menu.
Create a new key within your newly created command prompt named "command," and set the default string to
cmd.exe /k pushd %1
You may need to add %SystemRoot%\system32\ before the cmd.exe if the executable can't be found.

The changes should take place immediately. Right click a folder and your new menu item should appear.

Clean Duplicate records

DECLARE @Count int
DECLARE @name nvarchar(50)
DECLARE @culture_name nvarchar(50)

DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT name, culture_name , Count(*) - 1
FROM resource
GROUP BY name, culture_name
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @name, @culture_name, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM resource WHERE name = @name AND culture_name = @culture_name
SET ROWCOUNT 0

FETCH NEXT FROM dublicate_cursor INTO @name, @culture_name, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor

Find the table column order not matching

if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp

create table #tmp (database_name nvarchar(100), table_name nvarchar(100), column_name nvarchar(100), ordinal_position int)


EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use [?]
insert into #tmp
select db_name(), table_name , column_name , ordinal_position from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''users''
end
'
go

select * from #tmp;

select *
from #tmp t1
inner join #tmp t2 on t1.column_name = t2.column_name
where t1.ordinal_position <> t2.ordinal_position

A simple way to loop true the time counter

startme:
print convert(nvarchar(100),GETDATE(),109);
waitfor delay '00:00:01';

if(GETDATE() > '2010-03-24 15:18:00.713')
begin
goto endme;
end
else
begin
goto startme;
end

endme:

Displaying Execution Plans

Check the query IO, TIME, Execution Plan, Plan Text while running a query

Displaying Execution Plans1

Select Top 100 Slow Queries

SELECT DISTINCT TOP 100
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
WHERE s.execution_count > 1
ORDER BY AvgElapsedTime desc , s.max_elapsed_time DESC, ExecutionCount DESC;
GO

View SQL cached Plans and clean the execution cached plans

use Master
go
--clear cache
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go

USE master
GO
--view cached plans
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY dbid,usecounts DESC;
GO

sp_kill

use master
go

if exists (select name from sysobjects where name = 'sp_kill' and type = 'p' )
drop proc sp_kill
go

create proc sp_kill
@dbname varchar(100) = null, -- When specified, kills all spids inside of the database
@loginame varchar(50) = null, -- When specified, kills all spids under the login name
@hostname varchar(50) = null -- When specified, kills all spids originating from the host machine
as
begin
set nocount on
select spid, db_name(dbid) as 'db_name', loginame, hostname into #tb1_sysprocesses from master.dbo.sysprocesses (nolock)
declare @total_logins int, @csr_spid varchar(100)
set @total_logins = ( select count(distinct spid) from #tb1_sysprocesses )
if @dbname is null
begin
if @loginame is null
begin
if @hostname is null
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where hostname = @hostname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
--------------------------------------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame = @loginame and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end

end
-----------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where db_name = @dbname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end

end
drop table #tb1_sysprocesses
end

Tracking Changes in Your Enterprise Database

Tracking Changes in Your Enterprise Database

ASCII Character Codes

ASCII Character Codes

Is ISOLATION LEVEL READ UNCOMMITTED same as WITH (NOLOCK)

In one word, YES they are the same.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

Check here for more detailed information


Also here is another document :
Read Committed Isolation Level

Find out all FK CONSTRAINT that exist on a table

declare @tableName varchar(200)
set @tableName = 'ssis_tasks'

select * from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME in
(
select k.name from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName
)

Run SP automatically when SQL Server starts

EXECUTE sp_procoption
@procname = 'usp_StartTrace',
@optionname = 'startup',
@optionvalue = 'on'

Identify Primary Keys and PK column name

SELECT KU.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION

Change Tracking with SQL 2008

USE [Test]
GO

DROP TABLE [ChangeTrack]
GO

CREATE TABLE [dbo].[ChangeTrack](
[LineID] [int] IDENTITY(1000,1) NOT NULL,
[Fname] [nvarchar](100) NULL,
[Lname] [nvarchar](100) NULL,
[InsertDate] [datetime] NULL,
CONSTRAINT [PK_ChangeTrack] PRIMARY KEY CLUSTERED
(
[LineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChangeTrack] ADD CONSTRAINT [DF_ChangeTrack_InsertDate] DEFAULT (getdate()) FOR [InsertDate]
GO
ALTER TABLE [dbo].[ChangeTrack] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)
GO

declare @synchronization_version bigint , @last_synchronization_version bigint

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
waitfor delay '00:00:05'

--load table
insert into [ChangeTrack] (Fname, Lname ) values ('Pat','Rick'), ('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick')
waitfor delay '00:00:05'

--Obtain initial data set.
--SELECT * FROM [ChangeTrack]


--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1302
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'

--some updates
update [ChangeTrack] set Fname = Fname + '_CHANGED' where LineID % 10 = 4
waitfor delay '00:00:05'

--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1312
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'

--some insert/update/delete
insert into [ChangeTrack] (Fname, Lname ) values ('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111')
update [ChangeTrack] set Fname = '123' where LineID % 10 = 5
delete from [ChangeTrack] where LineID % 10 = 6
waitfor delay '00:00:05'

--select * from [ChangeTrack]

--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1313
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION()

PIVOT , UNPIVOT

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO

select * from pvt

--Unpivot the table.
SELECT VendorID, Employee, Orders
into #tmp
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

select * from #tmp
select * from pvt

-- pivot table
select *
from #tmp
pivot
(
sum(orders)
for employee in (Emp1, Emp2, Emp3, Emp4, Emp5)
) as p

---------------------------
--dynamic creation
declare @str nvarchar(1000)
set @str = ''
select @str = @str + employee+ ',' from #tmp group by employee
set @str = left(@str, LEN(@str)-1)
select @str

declare @sql nvarchar(1000)
set @sql = '
select *
from #tmp
pivot
(
sum(orders)
for employee in ('+@str+')
) as p'

print @sql
exec sp_executesql @sql
---------------------------

Data Type GEOMETRY usage to draw my face

SELECT geometry::Point(x, y, 0).STBuffer(0.4) As sig
FROM ( VALUES
(02,16),(02,17),(02,19),(02,20),(02,25),(02,26),(02,27),(02,28),(03,29),(03,30),(02,31),(02,32),(02,33),
(03,15),(03,22),(03,23),(03,24),(03,26),(03,27),(03,28),(03,29),(03,30),(03,31),(03,32),(03,33),(03,34),(03,35),(03,36),
(04,31),(04,32),(04,33),(04,34),(04,35),(04,36),(04,37),
(05,35),(05,36),(05,37),(05,38),(05,39),(05,40),
(06,23),(06,26),(06,37),(06,38),(06,39),(06,40),
(07,23),(07,24),(07,26),(07,38),(07,39),(07,40),(07,41),
(08,16),(08,22),(08,23),(08,24),(08,26),(08,27),(08,40),(08,41),(08,42),
(09,12),(09,13),(09,23),(09,24),(09,26),(09,27),(09,41),
(10,12),(10,13),(10,23),(10,24),(10,25),(10,26),(10,27),(10,43),
(11,13),(11,23),(11,24),(11,25),(11,39),(11,41),
(12,02),(12,13),(12,22),(12,23),(12,25),(12,38),(12,39),(12,40),(12,41),
(13,01),(13,02),(13,10),(13,13),(13,16),(13,17),(13,39),(13,40),(13,41),(13,42),
(14,01),(14,02),(14,07),(14,12),(14,39),(14,40),(14,41),(14,42),
(15,01),(15,07),(15,39),(15,40),(15,41),(15,42),(15,43),
(16,01),(16,15),(16,40),(16,41),(16,42),(16,43),
(17,01),(17,10),(17,16),(17,40),(17,41),(17,42),(17,43),
(18,01),(18,10),(18,13),(18,16),(18,40),(18,41),(18,42),(18,43),
(19,01),(19,10),(19,13),(19,16),(19,23),(19,24),(19,25),(19,39),(19,40),(19,41),(19,42),
(20,01),(20,06),(20,10),(20,11),(20,12),(20,22),(20,23),(20,24),(20,25),(20,26),(20,40),(20,41),(20,42),
(21,02),(21,03),(21,05),(21,06),(21,11),(21,12),(21,22),(21,23),(21,24),(21,25),(21,26),(21,40),(21,41),(21,42),
(22,02),(22,03),(22,12),(22,16),(22,17),(22,22),(22,23),(22,26),(22,27),(22,40),(22,41),
(23,03),(23,04),(23,05),(23,06),(23,12),(23,23),(23,24),(23,27),(23,40),(23,41),
(24,04),(24,05),(24,23),(24,24),
(25,04),(25,05),(25,06),(25,23),(25,24),(25,27),
(26,05),(26,06),(26,07),(26,23),(26,27),
(27,06),(27,07),(27,37),(27,38),(27,39),
(28,07),(28,22),(28,23),(28,24),(28,28),(28,29),(28,30),(28,31),(28,32),(28,35),(28,36),(28,37),(28,38),(28,39),
(29,08),(29,09),(29,24),(29,26),(29,27),(29,28),(29,29),(29,30),(29,31),(29,32),(29,33),(29,34),(29,35),(29,36),(29,37),(29,38),
(30,12),(30,28),(30,29),(30,30),(30,31),(30,32),(30,33),(30,34),(30,35),(30,36),
(31,12),(31,17),(31,18),(31,19),(31,20),(31,21),(31,22),(31,23),(31,24),(31,25),(31,26),(31,27),(31,28),(31,29),(31,30),(31,31),(31,32),(31,33),
(32,21),(32,22),(32,23),(32,24),(32,25),(32,26),(32,27),(32,28)

) AS Coordinates(x, y);

Merge tables Sample

select * from [target_stage]

Merge into [target] as t
using target_stage s on t.lineid = s.lineid

when matched then update
set t.fname = s.fname ,
t.lname = s.lname,
t.[ModifiedTimeStamp] = s.[ModifiedTimeStamp]

when not matched then insert ([LineID],[Fname],[Lname],[InsertDate],[ModifiedTimeStamp])
values (s.[LineID],s.[Fname],s.[Lname],s.[InsertDate],s.[ModifiedTimeStamp])

when not matched by source then delete

output INSERTED.Lineid;

select * from [target]

Executing a T-SQL batch multiple times using GO

Executing a T-SQL batch multiple times using GO

http://www.mssqltips.com/tip.asp?tip=1216

Search all columns in all the tables in a database for a specific value

Search all columns in all the tables in a database for a specific value:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2

SQL Naming & Coding Convention Standards

Valid Characters
Alphanumeric characters only ([a-zA-Z0-9]) – In every case only alphanumeric characters (and some situations underscore) are allowed
General Rules
The following rules should apply to all database objects.
• Clear and unambiguous naming.
o The name should provide as much information as possible about an object and provide an obvious connection to references in project documentation. Choice of names should typically consider a name space much larger than a project—preferably the enterprise. A name uniquely identifies a database object; its definition should be consistent wherever the object is used. What might seem clear in a narrow context may lose meaning outside of that context. Saving keystrokes should never be used as a criterion in selecting a name.
• Abbreviations should be avoided.
o Only when length restrictions apply should they be permitted. When used, abbreviations should follow the abbreviation rules.
o Example :
--Correct
DECLARE @Counter int
--Avoid
DECLARE @C int
• Alphanumeric characters only ([a-zA-Z0-9]) – Do not use special characters; restrict names to alphanumeric characters, no space is allowed in object name:
o Example :
--Avoid
CREATE TABLE dbo.[User Information]

• System naming should be avoided. Do not use any prefix or object names that somehow links to system object. No DB object can be created under “SYS” schema and there are limitations on prefixes on Stored procedures too, Please check Stored Procedure section for more details.
• Use Upper case for all T-SQL constructs (except types).
o Example : SELECT MAX(MyField) FROM MyTable
• Use Lower case for all T-SQL types and usernames:
o Example : DECLARE @MyVariable int



Database Object Naming Rules
Summary:
Object Prefix Suffix Alpha Numeric Characters Notes
Tables x Use singular form: Example User, not Users
Linking Tables _Link x Formed from the Tables they are linking, Example: A Table joining User and Group would be UserGroupLink
Table Columns x
Primary Key PK_ x
Clustered Index IXC_ x
Unique Clustered Index IXCU_ x
Unique Index IXU_ x
Index IX_ x
XML Index XML_IX_ x
XML Columns x Use .net casing, no underscores
Constraints CK_ x
Default Value DF_ x
Foreign Keys FK_ x
Views VW_ x
Functions FN_ x
Stored Procedures PR_ x
Triggers (after) TRGA_ x
Triggers (instead) TRGI_ x
CTE (Common Table Expressions) CTE_ x
Schemas
• Use lowercase for schema names.
• Always alias database objects using the schema name, even if this is the default [dbo] schema
• This applies to both CREATE statements and when referencing objects in FROM, INSERT or UPDATE statements etc.
Table Names
• Prefixes are used to identify the table with relation to application modules as much as possible. Some of the most common prefixes are as :
• ANALYTICS_
• AUDIT_
• BILLING_
• CAREER_
• CERT_
• CMP_
• COMMENT_
• COMPASSES_
• CONNECT_
• CORP_
• DESIGNATION_
• DEVPLAN_
• EMAIL_
• EVALUATION_
• LCMS_
• LICENSE_
• LO_
• OU_
• PO_
• PROXY_
• QTI_
• REPORT_
• RESUME_
• REVIEW_
• SECURITY_
• SMP_
• SOAP_
• TALENT_
• TASK_
• USER_
• WAREHOUSE_

• Alphanumeric characters only
• Always assign schema name to UDO’s (User Define Object) when defining or referencing
• Example :
--Correct
CREATE TABLE dbo.MyTable (...)
--Avoid
CREATE TABLE MyTable (...)

• Use the Singular Form Example : User, not Users
• Example :
--Correct
CREATE TABLE dbo.Address
--Avoid
CREATE TABLE dbo.Addresses

--Correct
SELECT * FROM dbo.MyTable (...)
--Avoid
SELECT * FROM MyTable (...)

Linking Table Names
• Linking Tables should be the name of the two tables it is joining, suffixed with _Link. Eg a joining table on User and Group would be User_Group_Link
Column Names
• Alpha-numeric
• No Prefix
• Format:
use the following components in the order below;
o Table Name: Primary keys only; Tables names are used to prefix all columns in dotted format, so this is not necessarily. The exception is the primary key since this is used in foreign keys.
o Qualifier: Optional; Description, to clarify the meaning of the field. For example, if a product has two images, this would clarify the field, eg. FrontImage and RearImage
o Name: Required; This is a database independent “datatype” descriptor which is used to classify the type of data. Below is a common list of standard classifiers. The exception to this is a Boolean. This should be Prefixed with “Is” as this more positively represents the meaning o the value. Flag suffix is considered optional “Flag” or Example. IsEnabled or IsEnabledFlag

Classifier Description Suggested SQL Data Type
Address Street or mailing address data nvarchar
Age Chronological age in years int
Average Average; consider a computed column numeric
Amount Currency amount money
Code Non Database Identifier
Count
Data A field containing extensible data xml
Date Calendar date smalldatetime
Datetime Date including time datetime
Day Day of month (1 - 31) tinyint
Description Brief narrative description nvarchar(MAX)
Duration Length of time, eg minutes int
ID Unique identifier for something int
Image A graphic image, such as a bitmap varbinary(MAX)
Flag Not Required: Flag indicates a boolean indicator, where the Qualifier verb does not make it clear it is a verb. Examples of a Qualifier are: Is, Has, Uses. Eg IsEnabled bit
Month Month of year
Name Formal name nvarchar
Number
Percent Number expressed as a percent
Quantity A number of things any numerical
Rate Number expressed as a rate any numerical
Ratio A proportion, or expression of relationship in quantity, size, amount, etc. between two things any numerical
Sequence A numeric order field int
Text Freeform textual information nvarchar(MAX)
Time Time of day smalldatetime
Title Formal name of something nvarchar
Version Timestamp timestamp
Weight Weight measurement any numerical
XML A field containing xml data xml
Year Calendar year or julian year number
Stored Procedure Names
• Naming Format: use the following components in the order below;
o Object: Required; usually the table or combinations of tables it is affecting, followed by underscore.
o Action: Required; eg Save, Load, Get, Set, SetSingle, Search, Delete
o Qualifier: Optional; additional descriptive words which help to clarify the specific meaning of the stored procedure
o Return Type: Optional; Indicates the type of data return
• Example Stored Procedure Names:
o pr_cmp_adjustment_save
o pr_cmp_adjustment_delete
o pr_cmp_adjustment_get
o pr_cmp_adjustment_search
• Do not:
o Use special characters.
o Use stored procedure group numbers (e.g. myProc;1).
o Prefix names with “dt_” or “sp_” or “xp_” as those are reserved for procedures shipped by SQL Server.
• Always create scope when defining Procedures and multi statement functions.
o Example :
--Correct
CREATE PROCEDURE dbo.uspMyProcedure
AS
BEGIN
(...)
END
--Avoid
CREATE PROCEDURE dbo.uspMyProcedure
AS
(...)
• When joining table always identify all columns with aliases and always alias the AS keyword.
o Example :
--Correct
SELECT U.Surname,
A.Street
FROM dbo.[User] AS U
JOIN dbo.Address AS A ON U.AddressID = A.AddressID
--Avoid
SELECT U.Surname,
Street –-Missing alias
FROM Users U –-Missing AS
JOIN dbo.Address ON U.AddressID = dbo.Address.AddressID
• When defining procedures and functions, include a commented Test Harness. Declare used variables for usage in testing:
o Example :
--Correct
CREATE FUNCTION dbo.tvfMyFunction
(
@MyParameter int
)
AS
/* TEST HARNESS
DECLARE @MyParameter int
SET @MyParameter = 1
SELECT * FROM dbo.tvfMyFunction(@MyParameter)
*/
(...)
--Correct
CREATE PROCEDURE dbo.uspMyProcedure
(
@MyParameter int
)
AS
/* TEST HARNESS
DECLARE @MyParameter int
SET @MyParameter = 1
BEGIN TRAN
SELECT * FROM dbo.MyTable –-MyTable before operation
EXEC dbo.uspMyProcedure(@MyParameter)
SELECT * FROM dbo.MyTable –-MyTable after operation
ROLLBACK TRAN
*/
(...)
--Avoid
/* TEST HARNESS
SELECT * FROM dbo.tvfMyFunction(1) –-argument not declared
*/

• Always individually name fields in INSERT and UPDATE statements. Never use the * operator in such statements.
o Example :
--Correct
INSERT INTO dbo.[User] (FirstName, LastName)
VALUES (@FirstName, @LastName)
--Avoid
INSERT INTO dbo.[User]
VALUES (@FirstName, @LastName)

• Set the NOCOUNT state as the first statement in all Procedures where you don’t specifically need the count returned.
o Example :
--Correct
CREATE PROCEDURE dbo.uspMyProcedure (...)
AS
BEGIN
SET NOCOUNT ON
(...)
• Set ISOLATION LEVEL READ UNCOMMITTED for retrieving data procs. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
o Example :
--Correct
Select FirstName, LastName FROM dbo.[User] With (Nolock)
--Avoid
Select * FROM dbo.[User]
User Defined Functions (UDF) Names
• Naming Format: use the following components in the order below;
o Prefix: Required; “FN_”
o Object: Required; usually the table or combinations of tables it is affecting, followed by underscore.
o Action: Required; eg Get, Set, SetSingle, Search, Delete
o Qualifier: Optional; additional descriptive words which help to clarify the specific meaning of the stored procedure
o Return Type: Optional; Indicates the type of data return
• Example Function Names:
o fn_get_credential_name
Parameters and Parameters Type - Stored Procedure/UDFs
 Example : Decalre @PageID int
 Data types should be lowercase, like “int”, “nvarchar(100)”
Variables - Stored Procedure/UDFs
 Example : Decalre @PageID int
 Data types should be lowercase, like “int”, “nvarchar(100)”
Recursive (CTE or while loop)
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix with “CTE_”
o Object: Required; usually the table being iterated over.
o Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata
• Example :
WITH CTE_Sales (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN CTE_Sales AS OS ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN CTE_Sales AS OM ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

• Note: Avoid the use of cursors where possible. Instead use a while loop
Updatable / Non Updatable View Names
For Views which are updatable, act as if they are a table.
This holds true for Updatable Partitioned Views.
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix with “VW_”
o Object: Required; usually related to the table(s) affected by the view
o Qualifier: Optional; additional descriptive words which help to clarify the purpose of the view.
o Example View Names are :
• vw_report_user_real_time
• vw_transcript_history
Trigger Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix with “TRG”
o Type: Required; depending on type of trigger, after or instead of. prefix with “A_” or “I_”
o Object: Required; usually the table being iterated over.
o Actions covered: Required; composite key of actions, “Upd”, “Ins”, “Del”
• Example Trigger Names:
o TRGA_CustomerInsUpdDe
o TRGA_ProductDel
o TRGI_AuthorUpd
• Note: Avoid the use of triggers where possible.
Primary Key Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix primary key with “PK_”
o TableName: Required; Table name of table being keyed
• Examples:
o PK_users
Index Names
Index names are unique within a table so it isn’t necessary to include the tablename in the index. When looking at execution plans it is helpful to have a hint about the columns being indexed
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix with “IX”
o Clustered: Required; if Clustered Index include “C”
o Unique: Required; if Unique Index include “U”
o Column Names: Required; Include the list of columns indexed, using underscores between the column names. For an index that covers all columns in the table, use the word All.
• Example Index Names:
o IXCU_user_ref (clustered unique)
o IXU_user_mgr (unique)
o IX_users_name_last_name_first (composite index)
o IXC_users_guid (clustered not unique)
Foreign Key Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix foreign key with “FK_”
o Reference Table Name(s): Required; Table name of table in the join, for which a unique index is on column(s) being linked. Where both have a unique index, such as linking key, order is optional
o Foreign Table Name(s): Required; Table name of table in the join, for there is not a unique index on the column(s) being linked.
• Example foreign key names:
o FK_users_user_type
o FK_users_users_status_local
Default Value Constraint Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix default value constraint with “DF_”
o TableName: Required; Table name
o ColumnName: Required; Column name
• Example foreign key names:
o DF_users_absent
Check Constraint Names
• Naming Format: use the following components in the order below;
o Prefix: Required; prefix check constraint with “CK_”
o TableName: Required; Table name
o Integer: Required; Where the integer id is used to distinguish the check constraint from other check constraints on the same table.
• Example foreign key names:
o CK_users1