MSSQL Toolbox

KillConnections.sql

-- 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

RunnigQueries.sql

-- 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

SearchObjectsByName.sql

-- 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%')

spmsforeachdb.sql

-- 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