Rename SQL installation

Am taking no credit for this.
Script from a colleague, it lets you rename a sql server, i.e. tell SQL about the fact that the server has been renamed!

DECLARE  @machine  SYSNAME,
        @instance SYSNAME

SELECT @instance = CASE
                  WHEN CHARINDEX('\',@@SERVERNAME) = 0 THEN ''
                  ELSE SUBSTRING(@@SERVERNAME,CHARINDEX('\',@@SERVERNAME),
                                   (LEN(@@SERVERNAME) + 1) - CHARINDEX('\',@@SERVERNAME))
                  END

SELECT @machine = CONVERT(NVARCHAR(100),SERVERPROPERTY('MACHINENAME')) + @instance;


EXEC SP_DROPSERVER
 @@SERVERNAME;

EXEC SP_ADDSERVER
 @machine ,'local'

NB : Remember to restart the SQL Server service after running the script.

TSQL 2005 - ALL

SQL Function to evaluate results of a subquery >

IF 130 > ALL (SELECT Rate FROM HumanResources.EmployeePayHistory)
 PRINT 'No employee is paid more than 130.'
ELSE
 PRINT 'There are employees paid more than 130.'

TSQL : Users & Logins

TSQL for querying systems objects for database users & logins (2000 & 2005) -

--sql 2000 -- list database users
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) -- exclude internal sql acounts

--sql 2005 -- list database users
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts

-- sql 2000 -- list orphanned users for current database
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 -- list orphanned users for current database
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins

-- code to demonstrate sql users present in db, but not mapping to sql server logins

select * from sys.sysusers
      join sys.syslogins
  on sys.sysusers.name = sys.syslogins.name
    and sys.sysusers.sid <> sys.syslogins.sid

--sql 2000 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sysusers where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins

TSQL : Blocking & Locking

Tasks waiting right now -

SELECT * FROM Sys.dm_os_waiting_tasks

Tasks being blocked right now -

SELECT * FROM Sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL

Bit difficult to test when your system is running fine, but a query for clearly seeing the details of blocking issues -

SELECT PROCESS_BLOCKING.SPID                 'Holding ID',
RTRIM(PROCESS_BLOCKING.STATUS)        'Status',
'Lock Type' = CASE SYSLOCKINFO.RSC_TYPE
WHEN 1 THEN NULL
WHEN 2 THEN 'DATABASE'
WHEN 3 THEN 'FILE'
WHEN 4 THEN 'INDEX'
WHEN 5 THEN 'TABLE'
WHEN 6 THEN 'PAGE'
WHEN 7 THEN 'KEY'
WHEN 8 THEN 'EXTENT'
WHEN 9 THEN 'RID'
WHEN 10 THEN 'APPLICATION'
ELSE NULL
END,
SUSER_SNAME(PROCESS_BLOCKING.SID)     'Holding User',
SUSER_SNAME(PROCESS_WAITING.SID)     'Waiting User',
PROCESS_WAITING.SPID                 'Waiting ID',
'Database' = CASE
WHEN SYSLOCKINFO.RSC_DBID = 0 THEN '[NULL]'
ELSE DB_NAME(SYSLOCKINFO.RSC_DBID)
END,
SYSLOCKINFO.RSC_OBJID            'Object ID',
RTRIM(PROCESS_BLOCKING.HOSTNAME)      'Holding Host',
RTRIM(PROCESS_WAITING.HOSTNAME)      'Waiting Host',
RTRIM(PROCESS_BLOCKING.PROGRAM_NAME)  'Holding Program',
RTRIM(PROCESS_WAITING.PROGRAM_NAME)  'Waiting Program',
PROCESS_BLOCKING.CMD                  'Holding Command',
PROCESS_WAITING.CMD                  'Waiting Command',
PROCESS_BLOCKING.CPU                  'CPU Time',
PROCESS_BLOCKING.PHYSICAL_IO          'I/O',
PROCESS_BLOCKING.MEMUSAGE             'Mem Usage'
FROM   MASTER.DBO.SYSLOCKINFO
JOIN MASTER.DBO.SYSPROCESSES PROCESS_BLOCKING
ON SYSLOCKINFO.REQ_SPID = PROCESS_BLOCKING.SPID
JOIN MASTER.DBO.SYSPROCESSES PROCESS_WAITING
ON SYSLOCKINFO.REQ_SPID = PROCESS_WAITING.BLOCKED
AND PROCESS_BLOCKING.SPID = PROCESS_WAITING.BLOCKED
WHERE  PROCESS_BLOCKING.SPID <> @@SPID

Find all internal sql objects (including undocumented ones)

Note is_ms_shipped clause below -

SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
AND [is_ms_shipped] = 1
ORDER BY [name];

To find functions, use type of  -
FN SQL Scalar Function
IF Inline Table Valued Function
TF SQL Table Valued Function

To find views, use type of  -
V View

To find procedures, use type of -
P SQL Stored Procedure
PC CLR Stored Procedure
X Extended Stored Procedure

ASP : Capturing SQL Injection attempts

if instr(Request.ServerVariables("QUERY_STRING"),"'") <> 0 THEN response.redirect "injectionattempt.asp?" & Request.ServerVariables("QUERY_STRING")
if instr(Request.ServerVariables("QUERY_STRING"),";") <> 0 THEN response.redirect "injectionattempt.asp?" & Request.ServerVariables("QUERY_STRING")
if instr(Request.ServerVariables("QUERY_STRING"),",") <> 0 THEN response.redirect "injectionattempt.asp?" & Request.ServerVariables("QUERY_STRING")

I use these lines at the top of pages that pass variables via the query string (the url itself). They search for ' , and ; which are characters which could break a sql query and enable someone to add a command into the sql being executed.

By calling injectionattempt.asp in this way I can capture the event and send myself an email letting me know this has occurred.

Determine SQL Authentication Method (2005)

Query to determine authentication method in use between client & server -

SELECT AUTH_SCHEME
FROM   SYS.DM_EXEC_CONNECTIONS
WHERE  SESSION_ID = @@SPID;

Possible return values are -

Login Type	Authentication Scheme
SQL	SQL
Windows
NTLM KERBEROS DIGEST BASIC NEGOTIATE

SQL 2005 : RANK() vs DENSE_RANK()

Using AdventureWorks to demonstrate RANK() vs DENSE_RANK() >

RANK() Skips line numbers after a tie in rank eg;
in these results 5, 9, 10, 11, 12 & 15 are missing >

select
RANK() OVER (ORDER BY count(*) desc) as DepartmentRank,
D.GroupName,
D.Name,
Count(*) as EmployeeCount
from
HumanResources.EmployeeDepartmentHistory  EDH
inner join HumanResources.Department D
on  EDH.DepartmentID = D.DepartmentID
where EndDate is Null
group by D.GroupName, D.Name
order by count(*) desc;

DENSE_RANK() Does not skips line numbers after a tie in rank.

select
DENSE_RANK() OVER (ORDER BY count(*) desc) as DepartmentRank,
D.GroupName,
D.Name,
Count(*) as EmployeeCount
from
HumanResources.EmployeeDepartmentHistory  EDH
inner join HumanResources.Department D
on  EDH.DepartmentID = D.DepartmentID
where EndDate is Null
group by D.GroupName, D.Name
order by count(*) desc;

Overriding Column Collation

This can be useful when joining across databases or in where clauses.
It effects columns where collation an issue i.e. text content.
Simply make both columns involved in the join/where clause the same collation

SELECT
   COLUMNLIST
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON t1.CHARCOLUMN COLLATE SQL_Latin1_General_CP1_CS_AS = t2.CHARCOLUMN COLLATE SQL_Latin1_General_CP1_CS_AS

replication : sp_get_distributor

Determining Replication setup using sp_get_distributor -

results on publisher
installed	distribution server	distribution db installed	is distribution publisher	has remote distribution publisher
1	SQL_DIST01	0	0	0
results on distributor
installed	distribution server	distribution db installed	is distribution publisher	has remote distribution publisher
1	SQL_DIST01	1	1	1
results on subscriber
installed	distribution server	distribution db installed	is distribution publisher	has remote distribution publisher
0	NULL	0	0	0

CROSS JOIN Example : Multiplying data rows

This example uses a CROSS JOIN to multiply data rows to a large result set.
The result will contain every combination of accountcode, month and year hence this technique is good for generating dimension tables.

-- create 3 temporary tables for the purpose of this demonstration

if object_id('tempdb..#accountcodes') is not null
 begin
 drop table #accountcodes
 end

create table #accountcodes (companycode char(10))

 insert into #accountcodes (companycode) values ('008')
 insert into #accountcodes (companycode) values ('009')
 insert into #accountcodes (companycode) values ('010')
 insert into #accountcodes (companycode) values ('011')


if object_id('tempdb..#years') is not null
 begin
 drop table #years
 end

create table #years (yearvalue int)

 insert into #years (yearvalue) values (2008)
 insert into #years (yearvalue) values (2007)
 insert into #years (yearvalue) values (2006)
 insert into #years (yearvalue) values (2005)
 insert into #years (yearvalue) values (2004)
 insert into #years (yearvalue) values (2003)

if object_id('tempdb..#months') is not null
 begin
 drop table #months
 end

create table #months (monthvalue int)

 insert into #months (monthvalue) values (1)
 insert into #months (monthvalue) values (2)
 insert into #months (monthvalue) values (3)
 insert into #months (monthvalue) values (4)
 insert into #months (monthvalue) values (5)
 insert into #months (monthvalue) values (6)
 insert into #months (monthvalue) values (7)
 insert into #months (monthvalue) values (8)
 insert into #months (monthvalue) values (9)
 insert into #months (monthvalue) values (10)
 insert into #months (monthvalue) values (11)
 insert into #months (monthvalue) values (12)


-- finally, perform the cross joins to get the final results.
-- there should be (4 x 6 x 12) 288 rows returned.

select * from #accountcodes cross join #years cross join #months

USP_DropTableConstraints_2005

Drops all constraints of the specified type from a table.
Takes database name, schema name, table name and constraint type as parameters.
Valid constraint types are ; 'PRIMARY KEY', 'FOREIGN KEY', 'CHECK'

Usage :
EXEC USP_DropTableConstraints_2005 'AdventureWorksTarget', 'Person', 'Contact', 'CHECK'

CREATE PROCEDURE USP_DropTableConstraints_2005
 @dbname VARCHAR(128),
 @schemaname VARCHAR(128),
 @tablename VARCHAR(128),
@constrainttype VARCHAR(128)
       
AS
-- USP_DropTableConstraints_2005 by sql solace
DECLARE  @sqlstring NVARCHAR(500)

SET @constrainttype = UPPER(LTRIM(RTRIM(@constrainttype)))

WHILE EXISTS (SELECT *
            FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE  CONSTRAINT_CATALOG = @dbname
            AND    TABLE_SCHEMA = @schemaname
            AND    TABLE_NAME = @tablename
     AND    CONSTRAINT_TYPE = @constrainttype)
BEGIN
  SELECT @sqlstring = 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP CONSTRAINT ' + CONSTRAINT_NAME
  FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE  CONSTRAINT_CATALOG = @dbname
  AND    TABLE_SCHEMA = @schemaname
  AND    TABLE_NAME = @tablename
  AND    CONSTRAINT_TYPE = @constrainttype
  PRINT @sqlstring
  EXECUTE sp_executesql  @sqlstring
END
GO