USE MASTER
GO
IF OBJECT_ID('dbo.sp_text') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_text AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_text
@ObjectName SYSNAME = NULL,
@Schema SYSNAME = NULL,
@Name SYSNAME = NULL,
@Type VARCHAR(2) = NULL,
@FilePath VARCHAR(MAX) = NULL,
@WildCharName BIT = 0,
@PrintSQL BIT = 1,
@PrintAnsi BIT = 0,
@GenerateAlter BIT = 1,
@SortInTempDb BIT = 1,
@OnlineIndex BIT = 0,
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF
IF @ObjectName IS NULL
AND @Schema IS NULL
AND @Name IS NULL
AND @Type IS NULL
BEGIN
PRINT '/* @Type values
FN : SQL scalar function
IF : SQL inline table-valued function
P : SQL Stored Procedure
TF : SQL table-valued-function
TR : SQL DML trigger
V : View
U : Table
*/
'
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_text',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
SET @ObjectName = Util.dbo.TrimBothEnds(@ObjectName)
DECLARE @OBJECT_ID INT = OBJECT_ID(@ObjectName),
@FoundObjectCount INT = 0
/* @Type values
AF : Aggregate function (CLR)
FN : SQL scalar function
FS : Assembly (CLR) scalar-function
FT : Assembly (CLR) table-valued function
IF : SQL inline table-valued function
P : SQL Stored Procedure
TF : SQL table-valued-function
TR : SQL DML trigger
V : View
U : Table
X : Extended stored procedure
*/
IF @OBJECT_ID IS NULL
PRINT '-- OBJECT ' + ISNULL(@ObjectName, 'NULL') + ' HAS NOT BEEN LOCATED. DOING A GENERAL SEARCH
GO'
IF OBJECT_ID('tempdb..#Indexes') IS NOT NULL
DROP TABLE #Indexes
CREATE TABLE #Indexes ([OBJECT_ID] INT NOT NULL
PRIMARY KEY CLUSTERED,
[Script] VARCHAR(MAX) NULL)
IF OBJECT_ID('tempdb..#OBJECTIDS') IS NOT NULL
DROP TABLE #OBJECTIDS
CREATE TABLE #OBJECTIDS (OBJECT_ID INT NOT NULL
PRIMARY KEY CLUSTERED,
SchemaName SYSNAME NOT NULL,
NAME SYSNAME NOT NULL,
TYPE VARCHAR(10) NOT NULL)
-- Search By Name Only
IF @ObjectName <> ''
AND @Schema IS NULL
AND @Name IS NULL
AND @Type IS NULL
BEGIN
INSERT #OBJECTIDS
(OBJECT_ID,
SchemaName,
NAME,
TYPE)
SELECT OBJECT_ID,
s.name,
o.name,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.objects o (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
WHERE type IN ('FN', 'IF', 'P', 'TF', 'TR', 'V')
AND OBJECT_ID = @OBJECT_ID
SET @FoundObjectCount = @@ROWCOUNT
IF @FoundObjectCount = 0
BEGIN
INSERT #OBJECTIDS
(OBJECT_ID,
SchemaName,
NAME,
Type)
SELECT OBJECT_ID,
s.name,
o.name,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.objects o (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
WHERE type IN ('FN', 'IF', 'P', 'TF', 'TR', 'V')
AND PARSENAME(@ObjectName, 2) IS NULL
AND o.name = PARSENAME(@ObjectName, 1)
OPTION (RECOMPILE)
SET @FoundObjectCount = @@ROWCOUNT
IF @FoundObjectCount = 0
BEGIN
INSERT #OBJECTIDS
(OBJECT_ID,
SchemaName,
NAME,
Type)
SELECT OBJECT_ID,
s.name,
o.name,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
CROSS APPLY (SELECT PARSENAME (@ObjectName, 2) AS ParsedSchema, PARSENAME (@ObjectName, 1) AS ParsedObject) ps
WHERE type IN ('FN', 'IF', 'P', 'TF', 'TR', 'V')
AND (ParsedSchema IS NULL
OR s.name LIKE '%' + ParsedSchema + '%')
AND ParsedObject <> ''
AND ((@WildCharName = 1
AND o.name LIKE '%' + ParsedObject + '%')
OR (@WildCharName = 0
AND CHARINDEX(ParsedObject, o.name, 0) > 0))
OPTION (RECOMPILE)
SET @FoundObjectCount = @@ROWCOUNT
IF @FoundObjectCount = 0
BEGIN
INSERT #OBJECTIDS
(OBJECT_ID,
SchemaName,
NAME,
Type)
SELECT OBJECT_ID,
s.name,
o.name,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.objects o (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
CROSS APPLY (SELECT PARSENAME (@ObjectName, 1) AS ParsedObject) ps
WHERE type IN ('FN', 'IF', 'P', 'TF', 'TR', 'V')
AND ((@WildCharName = 1
AND o.name LIKE '%' + ParsedObject + '%')
OR (@WildCharName = 0
AND CHARINDEX(ParsedObject, o.name, 0) > 0))
OPTION (RECOMPILE)
SET @FoundObjectCount = @@ROWCOUNT
END
END
END
IF @FoundObjectCount = 0
BEGIN
PRINT 'NO OBJECT FOUND MATCHING THE NAME!!
GO'
RETURN
END
ELSE
BEGIN
IF @FoundObjectCount > 1
PRINT '-- ' + CAST(@FoundObjectCount AS VARCHAR) + ' object(s) found matching name
GO
'
END
--#region Index script section
;
WITH IndexColumns
AS (SELECT pc.OBJECT_ID,
pc.SchemaName,
pc.[Name] AS ObjectName,
ix.NAME AS IndexName,
ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.type AS DataspaceType,
ds.name AS DataspaceName,
ix.index_id,
ix.is_unique,
ix.is_primary_key,
ix.is_unique_constraint,
ix.is_disabled,
ix.filter_definition,
ix.has_filter,
ISNULL(' WITH (' + NULLIF(LTRIM(RTRIM(SUBSTRING(ISNULL(', ' + dc.DataCompression, '')
+ ISNULL(CASE WHEN @SortInTempDb = 1 THEN ', SORT_IN_TEMPDB = ON'
END, '')
+ ISNULL(CASE WHEN ix.IGNORE_DUP_KEY = 1 THEN ', IGNORE_DUP_KEY = ON'
END, '') + ISNULL(CASE WHEN @OnlineIndex = 1 THEN ', ONLINE = ON'
END, '')
+ ISNULL(CASE WHEN ix.ALLOW_ROW_LOCKS = 0 THEN ', ALLOW_ROW_LOCKS = OFF'
END, '')
+ ISNULL(CASE WHEN ix.ALLOW_PAGE_LOCKS = 0 THEN ', ALLOW_PAGE_LOCKS = OFF'
END, '') + ISNULL(CASE WHEN ix.fill_factor > 0
THEN ', FILLFACTOR = '
+ CAST(ix.fill_factor AS VARCHAR)
END, '')
+ ISNULL(CASE WHEN ix.is_padded > 0 THEN ', PAD_INDEX = ON'
END, ''), 3, 8000))), '') + ')', '') AS IndexOptions,
ic.KeyColumns,
ic.IncludeColumns,
ic.PartitionedColumn
FROM (SELECT * FROM #OBJECTIDS WHERE Type = 'V') AS pc
INNER JOIN sys.indexes AS ix (NOLOCK) ON pc.OBJECT_ID = ix.OBJECT_ID
AND ix.index_id > 0
LEFT OUTER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = ix.data_space_id
LEFT OUTER JOIN sys.partitions AS pr (NOLOCK) ON pr.OBJECT_ID = pc.OBJECT_ID
AND pr.index_id = ix.index_id
AND pr.partition_number = 1
CROSS APPLY (SELECT CASE pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN 'ROW' THEN 'DATA_COMPRESSION = ROW'
WHEN 'PAGE' THEN 'DATA_COMPRESSION = PAGE'
END AS DataCompression) AS dc
CROSS APPLY (SELECT Util.dbo.StringConcat(CASE WHEN key_ordinal > 0
THEN QUOTENAME(ColumnName) + CASE WHEN is_descending_key = 1 THEN ' DESC'
ELSE ''
END
END, ', ') AS KeyColumns,
Util.dbo.StringConcat(CASE WHEN is_included_column = 1 THEN QUOTENAME(ColumnName)
END, ', ') AS IncludeColumns,
MAX(CASE WHEN partition_ordinal = 1 THEN QUOTENAME(ColumnName)
END) AS PartitionedColumn
FROM (SELECT TOP 999999
cl.name AS ColumnName,
ic.key_ordinal,
ic.is_included_column,
ic.partition_ordinal,
ic.is_descending_key
FROM sys.index_columns AS ic WITH (NOLOCK)
INNER JOIN sys.columns AS cl WITH (NOLOCK) ON cl.OBJECT_ID = ic.OBJECT_ID
AND cl.column_id = ic.column_id
WHERE ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
ORDER BY ic.key_ordinal,
ic.index_column_id) k) ic),
Indexes
AS (SELECT TOP 99999999
OBJECT_ID,
'IF NOT EXISTS (SELECT * FROM sys.schemas s (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id INNER JOIN sys.indexes i (NOLOCK) ON i.object_id = o.object_id WHERE s.name = '''
+ SchemaName + ''' AND o.name = ''' + ObjectName + ''' AND i.name = ''' + IndexName + ''')
' + CASE WHEN has_filter = 1 THEN 'SET ANSI_NULLS ON
' ELSE ''
END + CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1
THEN 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ADD CONSTRAINT ' + QUOTENAME(IndexName) + ' '
+ CASE WHEN is_primary_key = 1 THEN 'PRIMARY KEY '
ELSE 'UNIQUE '
END + IndexType + ' (' + KeyColumns + ')' + IndexOptions + ' ' + CASE DataspaceType
WHEN 'FG' THEN 'ON ' + QUOTENAME(DataspaceName)
WHEN 'PS'
THEN 'ON ' + QUOTENAME(DataspaceName) + '(' + PartitionedColumn + ')'
WHEN 'FD' THEN ''
ELSE ''
END
ELSE 'CREATE ' + CASE WHEN is_unique = 1 THEN 'UNIQUE '
ELSE ''
END + IndexType + ' INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ('
+ KeyColumns + ')' + ISNULL(' INCLUDE (' + IncludeColumns + ')', '') + ISNULL(' WHERE ' + filter_definition, '') + IndexOptions + ' '
+ CASE DataspaceType
WHEN 'FG' THEN 'ON ' + QUOTENAME(DataspaceName) + ''
WHEN 'PS' THEN 'ON ' + QUOTENAME(DataspaceName) + '(' + PartitionedColumn + ')'
WHEN 'FD' THEN ''
ELSE ''
END
END + '
' AS Script
FROM IndexColumns ic)
INSERT #Indexes
(OBJECT_ID,
script)
SELECT OBJECT_ID,
Util.dbo.StringConcat(Script, '
GO
') AS Script
FROM Indexes i
GROUP BY OBJECT_ID
OPTION (RECOMPILE)
--#endregion
SELECT @SQL = Util.dbo.StringConcat(CASE WHEN @PrintAnsi = 1
OR uses_ansi_nulls = 0
OR uses_quoted_identifier = 0 THEN '
SET ANSI_NULLS ' + CASE WHEN sm.uses_ansi_nulls = 1 THEN 'ON'
ELSE 'OFF'
END + '
GO
SET QUOTED_IDENTIFIER ' + CASE WHEN sm.uses_quoted_identifier = 1 THEN 'ON'
ELSE 'OFF'
END + '
GO
' ELSE ''
END + TrimmedDef + ISNULL('
GO
' + ix.Script, ''), '
GO
') + '
GO
'
FROM #OBJECTIDS id
INNER JOIN sys.objects o (NOLOCK) ON O.OBJECT_ID = id.OBJECT_ID
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.sql_modules sm (NOLOCK) ON sm.OBJECT_ID = o.OBJECT_ID
CROSS APPLY (SELECT CASE WHEN @GenerateAlter = 1 THEN Util.dbo.GenerateAlterScript(sm.definition)
ELSE Util.dbo.TrimMultiline(sm.definition)
END AS TrimmedDef) td
OUTER APPLY (SELECT Util.dbo.StringConcat(i.Script, '
GO
') AS Script
FROM #Indexes i
WHERE i.OBJECT_ID = id.OBJECT_ID) ix
OPTION (RECOMPILE)
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
RETURN
END ;
INSERT #OBJECTIDS
(OBJECT_ID,
SchemaName,
NAME,
TYPE)
SELECT o.OBJECT_ID,
s.name AS SchemaName,
o.Name,
o.Type
FROM sys.objects o (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
WHERE o.Type IN ('FN', 'IF', 'P', 'TF', 'TR', 'V')
AND (@ObjectName IS NOT NULL
AND o.OBJECT_ID = @OBJECT_ID)
OR (@ObjectName IS NULL
AND is_ms_shipped = 0
AND ((@Type IS NULL
OR o.type LIKE @Type)
AND (@schema IS NULL
OR s.name LIKE @schema)
AND (@Name IS NULL
OR (@Name IS NOT NULL
AND @WildCharName = 1
AND o.name LIKE '%' + @Name + '%')
OR (@Name IS NOT NULL
AND @WildCharName = 0
AND CHARINDEX(@Name, o.name, 0) > 0))))
OPTION (RECOMPILE)
SET @FoundObjectCount = @@ROWCOUNT
IF @FoundObjectCount = 0
BEGIN
PRINT 'NO OBJECT FOUND MATCHING FULL CRITERIA !!
GO'
RETURN
END
ELSE
BEGIN
IF @FoundObjectCount > 1
PRINT '-- ' + CAST(@FoundObjectCount AS VARCHAR) + ' object(s) FOUND matching FULL criteria
GO
'
END
--#region Index script section
;
WITH IndexColumns
AS (SELECT pc.OBJECT_ID,
pc.SchemaName,
pc.[Name] AS ObjectName,
ix.NAME AS IndexName,
ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.type AS DataspaceType,
ds.name AS DataspaceName,
ix.index_id,
ix.is_unique,
ix.is_primary_key,
ix.is_unique_constraint,
ix.is_disabled,
ix.filter_definition,
ix.has_filter,
ISNULL(' WITH (' + NULLIF(LTRIM(RTRIM(SUBSTRING(ISNULL(', ' + dc.DataCompression, '')
+ ISNULL(CASE WHEN @SortInTempDb = 1 THEN ', SORT_IN_TEMPDB = ON'
END, '') + ISNULL(CASE WHEN ix.IGNORE_DUP_KEY = 1 THEN ', IGNORE_DUP_KEY = ON'
END, '')
+ ISNULL(CASE WHEN @OnlineIndex = 1 THEN ', ONLINE = ON'
END, '')
+ ISNULL(CASE WHEN ix.ALLOW_ROW_LOCKS = 0 THEN ', ALLOW_ROW_LOCKS = OFF'
END, '')
+ ISNULL(CASE WHEN ix.ALLOW_PAGE_LOCKS = 0 THEN ', ALLOW_PAGE_LOCKS = OFF'
END, '') + ISNULL(CASE WHEN ix.fill_factor > 0
THEN ', FILLFACTOR = '
+ CAST(ix.fill_factor AS VARCHAR)
END, '')
+ ISNULL(CASE WHEN ix.is_padded > 0 THEN ', PAD_INDEX = ON'
END, ''), 3, 8000))), '') + ')', '') AS IndexOptions,
ic.KeyColumns,
ic.IncludeColumns,
ic.PartitionedColumn
FROM (SELECT * FROM #Procs WHERE Type = 'V') AS pc
INNER JOIN sys.indexes AS ix (NOLOCK) ON pc.OBJECT_ID = ix.OBJECT_ID
AND ix.index_id > 0
LEFT OUTER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = ix.data_space_id
LEFT OUTER JOIN sys.partitions AS pr (NOLOCK) ON pr.OBJECT_ID = pc.OBJECT_ID
AND pr.index_id = ix.index_id
AND pr.partition_number = 1
CROSS APPLY (SELECT CASE pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN 'ROW' THEN 'DATA_COMPRESSION = ROW'
WHEN 'PAGE' THEN 'DATA_COMPRESSION = PAGE'
END AS DataCompression) AS dc
CROSS APPLY (SELECT Util.dbo.StringConcat(CASE WHEN key_ordinal > 0 THEN QUOTENAME(ColumnName) + CASE WHEN is_descending_key = 1 THEN ' DESC'
ELSE ''
END
END, ', ') AS KeyColumns,
Util.dbo.StringConcat(CASE WHEN is_included_column = 1 THEN QUOTENAME(ColumnName)
END, ', ') AS IncludeColumns,
MAX(CASE WHEN partition_ordinal = 1 THEN QUOTENAME(ColumnName)
END) AS PartitionedColumn
FROM (SELECT TOP 999999
cl.name AS ColumnName,
ic.key_ordinal,
ic.is_included_column,
ic.partition_ordinal,
ic.is_descending_key
FROM sys.index_columns AS ic WITH (NOLOCK)
INNER JOIN sys.columns AS cl WITH (NOLOCK) ON cl.OBJECT_ID = ic.OBJECT_ID
AND cl.column_id = ic.column_id
WHERE ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
ORDER BY ic.key_ordinal,
ic.index_column_id) k) ic),
Indexes
AS (SELECT TOP 99999999
OBJECT_ID,
'IF NOT EXISTS (SELECT * FROM sys.schemas s (NOLOCK) INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id INNER JOIN sys.indexes i (NOLOCK) ON i.object_id = o.object_id WHERE s.name = '''
+ SchemaName + ''' AND o.name = ''' + ObjectName + ''' AND i.name = ''' + IndexName + ''')
' + CASE WHEN has_filter = 1 THEN 'SET ANSI_NULLS ON
' ELSE ''
END + CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1
THEN 'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ADD CONSTRAINT ' + QUOTENAME(IndexName) + ' '
+ CASE WHEN is_primary_key = 1 THEN 'PRIMARY KEY '
ELSE 'UNIQUE '
END + IndexType + ' (' + KeyColumns + ')' + IndexOptions + ' ' + CASE DataspaceType
WHEN 'FG' THEN 'ON ' + QUOTENAME(DataspaceName)
WHEN 'PS'
THEN 'ON ' + QUOTENAME(DataspaceName) + '(' + PartitionedColumn + ')'
WHEN 'FD' THEN ''
ELSE ''
END
ELSE 'CREATE ' + CASE WHEN is_unique = 1 THEN 'UNIQUE '
ELSE ''
END + IndexType + ' INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' ('
+ KeyColumns + ')' + ISNULL(' INCLUDE (' + IncludeColumns + ')', '') + ISNULL(' WHERE ' + filter_definition, '') + IndexOptions + ' '
+ CASE DataspaceType
WHEN 'FG' THEN 'ON ' + QUOTENAME(DataspaceName) + ''
WHEN 'PS' THEN 'ON ' + QUOTENAME(DataspaceName) + '(' + PartitionedColumn + ')'
WHEN 'FD' THEN ''
ELSE ''
END
END + '
' AS Script
FROM IndexColumns ic)
INSERT #Indexes
(OBJECT_ID,
script)
SELECT OBJECT_ID,
Util.dbo.StringConcat(Script, '
GO
') AS Script
FROM Indexes i
GROUP BY OBJECT_ID
OPTION (RECOMPILE)
--#endregion
SELECT @SQL = Util.dbo.StringConcat(CASE WHEN @PrintAnsi = 1
OR uses_ansi_nulls = 0
OR uses_quoted_identifier = 0 THEN '
SET ANSI_NULLS ' + CASE WHEN sm.uses_ansi_nulls = 1 THEN 'ON'
ELSE 'OFF'
END + '
GO
SET QUOTED_IDENTIFIER ' + CASE WHEN sm.uses_quoted_identifier = 1 THEN 'ON'
ELSE 'OFF'
END + '
GO
' ELSE ''
END + TrimmedDef + ISNULL('
GO
' + ix.Script, ''), '
GO
') + '
GO
'
FROM #OBJECTIDS o
INNER JOIN sys.sql_modules sm (NOLOCK) ON sm.OBJECT_ID = o.OBJECT_ID
CROSS APPLY (SELECT CASE WHEN @GenerateAlter = 1 THEN Util.dbo.GenerateAlterScript(sm.definition)
ELSE Util.dbo.TrimMultiline(sm.definition)
END AS TrimmedDef) td
OUTER APPLY (SELECT Util.dbo.StringConcat(i.Script, '
GO
') AS Script
FROM #Indexes i
WHERE i.OBJECT_ID = o.OBJECT_ID) ix
OPTION (RECOMPILE)
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
GO
EXEC sys.sp_ms_marksystemobject
sp_text
GO