Last active
February 22, 2017 23:54
-
-
Save thierryx96/b5011e80ad819b574ac9 to your computer and use it in GitHub Desktop.
MSSQL Toolbox
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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%') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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