Skip to content

Instantly share code, notes, and snippets.

@thierryx96
Last active February 22, 2017 23:54
Show Gist options
  • Save thierryx96/b5011e80ad819b574ac9 to your computer and use it in GitHub Desktop.
Save thierryx96/b5011e80ad819b574ac9 to your computer and use it in GitHub Desktop.
MSSQL Toolbox
-- Kill connections on a db
CREATE PROCEDURE dbo.clearDBUsers
@dbName SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255),
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID
PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
END
-- Get All Queries Ran on a Server
SELECT
E.[last_execution_time] AS [Date Time]
, ES.[text] AS [Script]
FROM sys.dm_exec_query_stats AS E
CROSS APPLY sys.dm_exec_sql_text(E.[sql_handle]) AS ES
ORDER BY E.[last_execution_time] DESC
-- Search Objects By Name
SELECT objects.name, objects.type_desc,OBJECT_DEFINITION(object_id)
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE 'CasinoId%' ESCAPE '\'
OR OBJECT_DEFINITION(object_id) LIKE '%CasinoID%' ESCAPE '\'
UNION
SELECT
objects.name, objects.type_desc,OBJECT_DEFINITION(object_id)
FROM sys.servers s
CROSS APPLY ( SELECT *
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE '%' + s.name
+ '%' ESCAPE '\'
) objects
WHERE is_linked = 1
AND (Data_Source LIKE '%CasinoID%' OR Data_Source LIKE 'CasinoId%')
-- Disclamer : https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb
declare @SQL varchar(max) = '
IF EXISTS (SELECT * FROM [?].sys.tables where name = ''table_name'')
BEGIN
-- examples
SELECT * FROM [?].dbo.table
SELECT ''?'' as DbName
END';
exec master.sys.sp_MSforeachdb @SQL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment