Kill all connections to a MS SQL database
This SQL Script will help you generate and execute a script to kill all open connections to the database of your choice. Remember to replace {databaseName} with your database name when you execute this script.
-- Create the sql to kill the active database connections
DECLARE @execSql VARCHAR(1000), @databaseName VARCHAR(100)
-- SET the database name for which to kill the connections
SET @databaseName = '{databaseName}'
SET @execSql = ''
SELECT @execSql = @execSql + 'KILL ' + CONVERT(CHAR(10), spid) + ' '
FROM master.dbo.sysprocesses
WHERE db_name(dbid) = @databaseName
AND DBID <> 0
AND spid <> @@spid
--Execute script
EXEC (@execSql)
Find foreign key dependencies
This SQL Script will help list out all the foreign key dependencies.
select cast(f.name as varchar(255)) as foreign_key_name
, r.keycnt
, cast(c.name as varchar(255)) as foreign_table
, cast(fc.name as varchar(255)) as foreign_column_1
, cast(fc2.name as varchar(255)) as foreign_column_2
, cast(p.name as varchar(255)) as primary_table
, cast(rc.name as varchar(255)) as primary_column_1
, cast(rc2.name as varchar(255)) as primary_column_2
from sysobjects f
inner join sysobjects c on f.parent_obj = c.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects p on r.rkeyid = p.id
inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
where f.type = 'F'
ORDER BY cast(p.name as varchar(255)) collapse
Few useful DateTime functions
----Today
SELECT GETDATE() 'Today'
--from first second of today
SELECT dateadd(second, 0, dateadd(day, datediff(day, 0, getdate()), 0))
--from last second of today
SELECT dateadd(second, -1, dateadd(day, datediff(day, 0, getdate())+1, 0))
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'
Take Database offline
This SQL Script will help you take the database of your choice offline. Remember to replace {databaseName} with your database name when you execute this script.
ALTER DATABASE {databaseName} SET OFFLINE WITH ROLLBACK IMMEDIATE
Grant SELECT permission to User/Role on all Tables in a database
This SQL Script will help you generate a script to grant select permission to a user/role on all tables in the database of your choice. Remember to replace {databaseName} with your database name when you execute this script.
USE {databaseName}
GO
SELECT 'GRANT SELECT ON ' + TABLE_NAME + ' TO REPORTS' FROM INFORMATION_SCHEMA.Tables
Disable/Enable all the foreign key constraints in a database
Remember to replace {databaseName} with your database name when you execute this script.
USE {databaseName}
GO
-- Disable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
GO
-- Enable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"