SQL scripts

!SQL scripts

This is a collection of useful SQL scripts that are either templates or fully implemented functionalitites.
Each script includes a description about itself.

Create schema if not exists.sql

-- ## This script will check if a database schema with the givn name exists and if it doesn't - creates it. ##
DECLARE @SchemaName nvarchar(max) = 'desired schema name'


IF (SCHEMA_ID(@SchemaName) IS NULL) 
BEGIN
    EXEC ('CREATE SCHEMA ' + @SchemaName + ' AUTHORIZATION [dbo]')
END

Find string in all stored procedures.sql

-- ## This script searches through all stored procedures' bodies ##
-- ## for a given string and displays the ones that contain it.  ##

DECLARE @SearchString nvarchar(max) = 'text to search for'

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
  FROM sys.sql_modules m
       INNER JOIN
       sys.objects o ON m.object_id = o.object_id
 WHERE m.definition Like '%' + @SearchString + '%'

Get all events of class.sql

-- ## This script queries the "sys" schema for events of a certain type and displays them ##

DECLARE @Path NVARCHAR(260)
DECLARE @EventClass INT = 0	-- The class of events you wish shown.

SELECT @Path = REVERSE(SUBSTRING(REVERSE([path]), 
       CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM   sys.traces
WHERE  is_default = 1

SELECT TextData, ApplicationName, LoginName, StartTime, DatabaseName
  FROM sys.fn_trace_gettable(@Path, DEFAULT)
  WHERE EventClass = @EventClass
  AND DatabaseName = DB_NAME()
  ORDER BY StartTime DESC

Get executed queries on server.sql

-- ## This script will show all queries that have been executed on this database.
-- ## This is especially useful in cases where SQL Server Profiler is not available, for example Azure SQL databases.

SELECT
  deqs.last_execution_time AS [Time],
  dest.TEXT AS [Query]
FROM
  sys.dm_exec_query_stats AS deqs
CROSS APPLY
  sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY
  deqs.last_execution_time DESC

Kill all connections to database.sql

-- ## This script will kill all connections to the database with name "@DbName". ##

USE [master];

DECLARE @DbName varchar(100) = 'Name of the database whose connections to kill'


DECLARE @Kill varchar(8000) = ''; -- Do not change.
SELECT @Kill = @Kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id(@DbName)

EXEC(@Kill);

Search all tables for value.sql

-- ## This script will search all records in all tables for the text in "@SearchStr" ##
-- ## and display which columns in which tables contain it.                          ##

use [dbName]

DECLARE @SearchStr nvarchar(max) = 'Text to search for'
 
 
    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
    SET NOCOUNT ON
 
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
    WHILE @TableName IS NOT NULL
     
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )
 
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
             
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
     
            IF @ColumnName IS NOT NULL
             
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END
 
    SELECT ColumnName, ColumnValue FROM #Results
 
DROP TABLE #Results

Template full error catching.sql

-- ## This is a template for an SQL query where in case of any error nothing will hang or be committed. ##

SET XACT_ABORT ON


BEGIN TRY
	BEGIN TRANSACTION [TransactionName]
	
	  -- actual query
  
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
	SELECT ERROR_MESSAGE() as 'Error Message'
END CATCH