SQL Templates

!SQL Templates

SQL Templates

Cursor.sql

DECLARE theCursor CURSOR 
	FOR /* SELECT QUERY */

DECLARE @CursorVariable nvarchar(MAX)

OPEN theCursor
FETCH NEXT FROM theCursor
INTO @CursorVariable

WHILE @@FETCH_STATUS = 0
BEGIN
	/* Do stuff with cursor variables */
	SELECT @CursorVariable

	FETCH NEXT FROM theCursor
	INTO @CursorVariable
END

CLOSE theCursor
DEALLOCATE theCursor

Date Range.sql

-- ============================================================================================
--	DATE RANGE
--
--  Get's a date range from 00:00:00.000 of the Start Date to 23:59:59.000 of the End Date
-- --------------------------------------------------------------------------------------------
--	To use:                                                      
--      Keyboard: Ctrl+Shift+M  [or]  Menu: Query > Specify Values for Template Parameters...
--		Enter values for "Start Date" and "End Date" 
--		Click OK	
--		Click Execute or Press F5								
-- ============================================================================================

DECLARE @Date datetime
SET @Date = getdate()

SELECT  DATEADD(d, DATEDIFF(d, 0, <Start Date, DateTime, GETDATE()>), 0) 'Start',
		DATEADD(s, -1, DATEADD(d, DATEDIFF(d, 0, <End Date, DateTime, GETDATE()>), 1)) 'End'

SP Search v1.sql

USE <DatabaseName, sysname, >
DECLARE @StringToSearch nvarchar(255) = '%<Search value, varchar(100),>%'

/*==============================================================*/
/* SEARCH SYSTEM OBJECTS FOR SPECIFIED TEXT						*/
/*--------------------------------------------------------------*/
/* USE: Press Ctrl+Shift+M										*/
/*		Enter Search value and target database then click OK	*/
/*		Click Execute or Press F5								*/
/*==============================================================*/

SELECT o.name 'Stored Procedures'
FROM SYSOBJECTS o (NOLOCK)
INNER JOIN SYSCOMMENTS c (NOLOCK)
	ON o.id = c.id
WHERE (
	c.text LIKE @StringToSearch
	AND o.xtype = 'P'
)
GROUP BY o.name
ORDER BY o.name

SELECT o.name 'User Defined Functions'
FROM SYSOBJECTS o (NOLOCK)
INNER JOIN SYSCOMMENTS c (NOLOCK)
	ON o.id = c.id
WHERE (
	c.text LIKE @StringToSearch
	AND o.xtype = 'U'
)
GROUP BY o.name
ORDER BY o.name

SELECT o.name 'Views'
FROM SYSOBJECTS o (NOLOCK) 
INNER JOIN SYSCOMMENTS c (NOLOCK)
	ON o.id = c.id
WHERE (
	c.text LIKE @StringToSearch
	AND o.xtype = 'V'
)
GROUP BY o.name
ORDER BY o.name 

SELECT o.name 'Tables'
FROM SYSCOLUMNS c (NOLOCK) 
INNER JOIN SYSOBJECTS o (NOLOCK)
	ON o.id = c.id
INNER JOIN MASTER.DBO.SYSTYPES t
ON c.xtype = t.xtype
WHERE (
	o.name LIKE @StringToSearch
	AND o.Type = 'U'
)
GROUP BY o.name
ORDER BY o.name

;WITH CTE AS (
	SELECT 		
		'[' + o.name + '].[' + c.name +']' Columns
	FROM SYSCOLUMNS c (NOLOCK) 
	INNER JOIN SYSOBJECTS o (NOLOCK)
		ON o.id = c.id
	INNER JOIN MASTER.DBO.SYSTYPES t
	ON c.xtype = t.xtype
	WHERE (
		c.name LIKE @StringToSearch
		AND o.Type = 'U'
	)
)
SELECT *
FROM CTE
WHERE CHARINDEX('@',Columns) = 0
GROUP BY Columns
ORDER BY Columns

GO

SP Search v2.sql

-- ===========================================================================================
--	DATABASE OBJECT SEARCH                                       
-- -------------------------------------------------------------------------------------------
--	To use:                                                      
--      Keyboard: Ctrl+Shift+M  [or]  Menu: Query > Specify Values for Template Parameters...
--		Enter values for "Search value" and "DatabaseName" 
--		Click OK	
--		Click Execute or Press F5								
-- ===========================================================================================


-- User modifiable variables
USE <DatabaseName, sysname, >
DECLARE @SearchPattern nvarchar(255) = '%<Search value, varchar(100),>%'

-- Script variables (DO NOT MODIFY)
DECLARE @SurroundingCharCount int, 
		@SearchPatternLength int,
		@Marker_L nvarchar(5),
		@Marker_R nvarchar(5)

SET @SearchPatternLength = LEN(@SearchPattern) - 2
SET @SurroundingCharCount = 30
SET @SurroundingCharCount = CASE WHEN @SearchPatternLength > @SurroundingCharCount THEN @SearchPatternLength ELSE @SurroundingCharCount END

SET @Marker_L = '>>>'
SET @Marker_R = '<<<'


-- Find objects with names that contain the search pattern
SELECT 
	s.name + '.' + o.name as 'Object Name',
	CASE o.Type
		WHEN 'U' THEN 'Table'
		WHEN 'V' THEN 'View'
		WHEN 'P' THEN 'Stored Procedure'
		WHEN 'PC' THEN 'Stored Procedure (CLR)'
		WHEN 'FN' THEN 'User Defined Function (Scalar)'
		WHEN 'IF' THEN 'User Defined Function (Inline)'
		WHEN 'TF' THEN 'User Defined Function (Multi)'
		ELSE 'Unrecognized (' + o.Type + ')'
	END as 'Type Description',
	CASE WHEN PATINDEX(@SearchPattern, o.name) > 0 THEN
	(
		RTRIM(LTRIM(
			SUBSTRING(o.name, 0, PATINDEX(@SearchPattern, o.name)) + 
			@Marker_L + SUBSTRING(o.name, PATINDEX(@SearchPattern, o.name), @SearchPatternLength) + @Marker_R +
			SUBSTRING(o.name, PATINDEX(@SearchPattern, o.name) + @SearchPatternLength, LEN(o.name))
		)) 
	) END as 'Match'
FROM sys.objects o (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK)
	ON o.[schema_id] = s.[schema_id]
WHERE o.name LIKE @SearchPattern
  AND o.Type NOT IN ('D', 'F', 'IT', 'PK', 'S', 'SQ', 'UQ')
ORDER BY [Type Description], o.name


-- Find stored procedures and UDFs that contain the search pattern
;WITH CTE (ObjectName, TypeDescription, Definition, LeadingIndex, LeadingLength, PatternIndex, PatternLength, TrailingIndex, TrailingLength) 
AS (
	SELECT 
		s.name + '.' + o.name,
		CASE o.Type
			WHEN 'P' THEN 'Stored Procedure'
			WHEN 'PC' THEN 'Stored Procedure (CLR)'
			WHEN 'FN' THEN 'User Defined Function (Scalar)'
			WHEN 'IF' THEN 'User Defined Function (Inline)'
			WHEN 'TF' THEN 'User Defined Function (Multi)'
		END,
		m.Definition,	
		CASE WHEN PATINDEX(@SearchPattern, m.definition) - @SurroundingCharCount <= 0 
			THEN 1 
			ELSE PATINDEX(@SearchPattern, m.definition) - @SurroundingCharCount 
		END, 
		PATINDEX(@SearchPattern, m.definition) - CASE WHEN PATINDEX(@SearchPattern, m.definition) - @SurroundingCharCount <= 0 
													THEN 1 
													ELSE PATINDEX(@SearchPattern, m.definition) - @SurroundingCharCount 
												 END,
		PATINDEX(@SearchPattern, m.definition), 
		@SearchPatternLength,
		PATINDEX(@SearchPattern, m.definition) + @SearchPatternLength, 
		@SurroundingCharCount
	FROM sys.objects o (NOLOCK)
	INNER JOIN sys.schemas s (NOLOCK)
		ON o.[schema_id] = s.[schema_id]
	LEFT JOIN sys.sql_modules m (NOLOCK)
		ON o.[object_id] = m.[object_id]
	WHERE (o.name LIKE @SearchPattern OR m.definition LIKE @SearchPattern)
	  AND o.Type IN ('P','PC','FN','IF','TF')
)
SELECT 
	ObjectName as 'Object Name',
	TypeDescription as 'Type Description',
	CAST(PatternIndex as nvarchar) + '-' + CAST(PatternIndex+PatternLength as nvarchar) as 'Position',
	(
		RTRIM(LTRIM(
			SUBSTRING(Definition, LeadingIndex, LeadingLength) +
			@Marker_L + SUBSTRING(Definition, PatternIndex, PatternLength) + @Marker_R +
			SUBSTRING(Definition, TrailingIndex, TrailingLength)
		)) 
	) as 'First match'
FROM CTE
ORDER BY TypeDescription, ObjectName


-- Find column names that contain the search pattern
;WITH CTE (TableName, ColumnName) 
AS (
	SELECT 		
		s.name + '.' + o.name,
		c.name
	FROM sys.objects o (NOLOCK)
	INNER JOIN sys.columns c (NOLOCK) 
		ON c.[object_id] = o.[object_id]
	INNER JOIN sys.schemas s (NOLOCK)
		ON o.[schema_id] = s.[schema_id]
	WHERE c.name LIKE @SearchPattern
	  AND o.Type = 'U'
)
SELECT 
	TableName as 'Table',
	ColumnName as 'Column'
FROM CTE
ORDER BY TableName, ColumnName

sp_FindMyData_String.sql

CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)

    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'Base Table'
            And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')


DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                            THEN 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       = ''' + @DataToFind + '''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            ELSE 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       Like ''%' + @DataToFind + '%''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            END,
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i


        PRINT @SQL
        EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

        SET @i = @i + 1
    END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

Try Transaction v1.sql

BEGIN TRY
	BEGIN TRANSACTION



	COMMIT
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK
	
	DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
	SELECT @ErrMsg = ERROR_MESSAGE(),
		 @ErrSeverity = ERROR_SEVERITY()

	RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Try Transaction v2.sql

DECLARE @EnableCommit bit
SET @EnableCommit = 0

BEGIN TRY
	BEGIN TRANSACTION
	/* BEGIN */



	/* END */
	IF (@EnableCommit = 1)
	BEGIN
		COMMIT
		PRINT 'Success!'
	END 
	ELSE BEGIN
		ROLLBACK
		PRINT 'Success! (Rolled back)'
	END
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK
	
	DECLARE @ErrorMessage nvarchar(4000), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int, @ErrorProcedure nvarchar(200)

    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE()
		
	SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine)

END CATCH