USE MASTER
GO
IF OBJECT_ID('dbo.sp_objects') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_objects AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_objects
@Name SYSNAME = NULL,
@Schema SYSNAME = NULL,
@Type VARCHAR(1000) = NULL,
@TypeDelimiter VARCHAR(30) = ',',
@IgnoreSchemaList VARCHAR(MAX) = 'cdc,sys,Metadata,tTaxMat,tTaxProp,tTransMat,tTransProp',
@IgnoreSchemaDelimiter VARCHAR(30) = ',',
@WildCharName BIT = 0,
@PrintScript BIT = 1,
@ScriptMostNumberOfObjects INT = 20,
@GenerateAlter BIT = 0,
@PrintHelp BIT = 1,
@GetMostRecent INT = NULL,
@SortInTempDb BIT = 0,
@OnlineIndex BIT = 0,
@FilePath VARCHAR(4000) = NULL /* Will write the generated sql to the path */
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @PrintHelp = 1
AND @Name IS NULL
AND @Schema IS NULL
AND @Type IS NULL
AND ISNULL(@TypeDelimiter, ',') = ','
AND ISNULL(@IgnoreSchemaList, '') = 'cdc,sys,Metadata,tTaxMat,tTaxProp,tTransMat,tTransProp'
AND ISNULL(@IgnoreSchemaDelimiter, ',') = ','
AND @ScriptMostNumberOfObjects = 20
AND ISNULL(@GetMostRecent, 0) = 0
PRINT '/*
Available values for @Type parameter
P : Stored procedure
FN : SQL scalar function
IF : SQL inline table-valued function
TF : Table Valued Function
V : View
TR : SQL DML trigger
FS : Assembly (CLR) scalar-function
FT : Assembly (CLR) table-valued function
PC : Assembly (CLR) stored-procedure
RF : Replication-filter-procedure
X : Extended stored procedure
TA : Assembly (CLR) DML trigger
*/
'
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_objects',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
PRINT 'GO'
/* @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
*/
IF @WildCharName = 1
AND @Name NOT LIKE '%[%]%'
SET @Name = '%' + Util.dbo.TrimBothEnds(@Name) + '%'
IF ISNULL(@ScriptMostNumberOfObjects, 0) = 0
SET @ScriptMostNumberOfObjects = 9999999999
DECLARE @Top INT = ISNULL(NULLIF(@GetMostRecent, 0), 99999999),
@OBJECT_ID INT = OBJECT_ID(Util.dbo.TrimBothEnds(@Name))
DECLARE @Types TABLE (TYPE VARCHAR(5) PRIMARY KEY CLUSTERED)
SELECT @TypeDelimiter = ISNULL(NULLIF(Util.dbo.TrimBothEnds(@TypeDelimiter), ''), ','),
@IgnoreSchemaDelimiter = ISNULL(NULLIF(Util.dbo.TrimBothEnds(@IgnoreSchemaDelimiter), ''), ',')
INSERT @Types
(TYPE)
SELECT DISTINCT
CAST(Field AS VARCHAR(5))
FROM Util.dbo.ParseDelimited(@Type, @TypeDelimiter)
WHERE Field <> ''
AND @OBJECT_ID IS NULL
UNION
SELECT TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.objects o (NOLOCK)
WHERE OBJECT_ID = @OBJECT_ID
AND @OBJECT_ID IS NOT NULL
IF OBJECT_ID('TEMPDB..#Procs') IS NOT NULL
DROP TABLE #Procs
CREATE TABLE #Procs (OBJECT_ID INT NOT NULL
PRIMARY KEY CLUSTERED,
[FQN] SYSNAME NOT NULL,
[SchemaName] SYSNAME NOT NULL,
[Name] SYSNAME NOT NULL,
[TypeName] SYSNAME NOT NULL,
[TYPE] VARCHAR(30) NOT NULL,
[create_date] DATETIME NOT NULL,
[modify_date] DATETIME NOT NULL)
DECLARE @SQL VARCHAR(MAX)
INSERT #Procs
([OBJECT_ID],
[FQN],
[SchemaName],
[Name],
[TypeName],
[TYPE],
[create_date],
[modify_date])
SELECT TOP (@Top)
t.OBJECT_ID,
'[' + s.NAME + '].[' + t.NAME + ']' AS FQN,
s.name AS [SchemaName],
t.name AS [Name],
so.TypeName,
so.CreateDefinition AS [TYPE],
t.create_date,
t.modify_date
FROM sys.objects AS t (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK) ON t.SCHEMA_ID = s.SCHEMA_ID
LEFT OUTER JOIN Util.dbo.SystemObjectTypes so ON so.Type = t.type COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE so.CreateDefinition IN ('FUNCTION', 'VIEW', 'TRIGGER', 'PROCEDURE')
AND NOT EXISTS ( SELECT *
FROM Util.dbo.ParseDelimited(@IgnoreSchemaList, @IgnoreSchemaDelimiter)
WHERE Field = s.name )
AND ((@OBJECT_ID IS NOT NULL
AND t.OBJECT_ID = @OBJECT_ID)
OR ((@OBJECT_ID IS NULL
AND t.is_ms_shipped = 0)
AND NOT EXISTS ( SELECT *
FROM Util.dbo.ParseDelimited(@IgnoreSchemaList, @IgnoreSchemaDelimiter)
WHERE Field = s.name )
AND ((@Name IS NULL
OR (@Name IS NOT NULL
AND @WildCharName = 1
AND t.name LIKE @Name)
OR (@Name IS NOT NULL
AND @WildCharName = 0
AND CHARINDEX(@Name, t.NAME, 0) > 0))
AND (@Schema IS NULL
OR s.name LIKE @Schema)
AND (@Type IS NULL
OR EXISTS ( SELECT *
FROM @Types b
WHERE t.type COLLATE SQL_Latin1_General_CP1_CI_AS = b.TYPE )))))
ORDER BY CASE WHEN @Top > 0 THEN modify_date
ELSE GETDATE()
END DESC
OPTION (RECOMPILE)
SELECT [FQN],
[SchemaName],
[Name],
[TypeName],
[Type],
[create_date],
[modify_date]
FROM #Procs
IF @PrintScript = 1
OR @FilePath <> ''
AND @@ROWCOUNT BETWEEN 1 AND @ScriptMostNumberOfObjects
BEGIN
--#region Index script section
IF OBJECT_ID('tempdb..#Indexes') IS NOT NULL
DROP TABLE #Indexes
CREATE TABLE #Indexes ([OBJECT_ID] INT NOT NULL,
[Script] VARCHAR(MAX) NULL) ;
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 = ISNULL((SELECT Util.dbo.StringConcat(CASE WHEN ISNULL(@GenerateAlter, 0) = 0
THEN '--/*DROP*/ IF OBJECT_ID(''' + [FQN] + ''') IS NOT NULL DROP ' + t.Type + ' ' + [FQN]
+ '
GO
' ELSE ''
END + TrimmedDef + ISNULL('
GO
' + ix.Script, ''), '
GO
') + '
GO
'
FROM #Procs t
INNER JOIN sys.sql_modules sm (NOLOCK) ON sm.OBJECT_ID = t.OBJECT_ID
LEFT OUTER JOIN #Indexes ix ON ix.OBJECT_ID = t.OBJECT_ID
AND t.TYPE = 'V'
CROSS APPLY (SELECT CASE WHEN @GenerateAlter = 1 THEN Util.dbo.GenerateAlterScript(sm.definition)
ELSE Util.dbo.TrimMultiLine(sm.definition)
END AS TrimmedDef) td), '')
+ ISNULL((SELECT Util.dbo.StringConcat(CASE WHEN ISNULL(@GenerateAlter, 0) = 0
THEN '--/*DROP*/ IF OBJECT_ID(''' + [FQN] + ''') IS NOT NULL DROP ' + t.Type + ' ' + [FQN] + '
GO
CREATE ' + fd.ProcDefinition + '
GO
' ELSE 'ALTER ' + fd.ProcDefinition + '
GO
'
END, '')
FROM #Procs t
INNER JOIN sys.objects o (NOLOCK) ON o.OBJECT_ID = t.OBJECT_ID
INNER JOIN sys.assembly_modules m (NOLOCK) ON o.OBJECT_ID = m.OBJECT_ID
INNER JOIN sys.assemblies a (NOLOCK) ON a.assembly_id = m.assembly_id
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
LEFT OUTER JOIN sys.database_principals dp (NOLOCK) ON dp.principal_id = m.execute_as_principal_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
CROSS APPLY (SELECT 'RETURNS TABLE (
' + Util.dbo.StringConcat(' ' + QUOTENAME(c.NAME) + ' ' + ct.ColumnType + CASE WHEN c.is_nullable = 1 THEN ' NULL'
ELSE ' NOT NULL'
END, ',
') + '
)' AS ReturnTable
FROM sys.columns c (NOLOCK)
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = c.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.PRECISION, c.scale, c.collation_name, db.collation_name) ct
WHERE c.OBJECT_ID = m.OBJECT_ID
AND o.type = 'FT' -- Assembly (CLR) table-valued function
) rt
CROSS APPLY (SELECT '(' + Util.dbo.StringConcat(FuncInParam, ', ') + ')' AS FuncInParams,
'RETURNS ' + Util.dbo.StringConcat(FuncOutParam, ',') AS FuncOutParam,
Util.dbo.StringConcat(' ' + ProcParam, ',
') AS ProcParams
FROM sys.parameters pr (NOLOCK)
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = pr.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, pr.max_length, pr.PRECISION, pr.scale, db.collation_name, db.collation_name) ct
CROSS APPLY (SELECT CASE WHEN pr.parameter_id = 0 THEN ct.ColumnType
END AS FuncOutParam,
CASE WHEN pr.parameter_id > 0
THEN pr.name COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + ct.ColumnType
END AS FuncInParam,
CASE WHEN pr.parameter_id > 0
THEN pr.name COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + ct.ColumnType
+ CASE WHEN pr.has_default_value = 1
THEN ' = '
+ CASE WHEN y.name LIKE '%char'
OR y.name = 'sysname'
OR y.name = 'xml'
THEN ISNULL(REPLACE(CAST (default_value AS VARCHAR(MAX)), '''', ''''''),
'NULL')
WHEN y.name LIKE '%date%'
OR y.name LIKE '%time%'
THEN ISNULL(CAST (pr.default_value AS VARCHAR(MAX)), 'NULL')
WHEN y.collation_name IS NULL
AND pr.PRECISION > 0
AND y.name NOT LIKE '%date%'
AND y.name NOT LIKE '%time%'
THEN CAST (pr.default_value AS VARCHAR)
ELSE ISNULL(CAST (default_value AS VARCHAR(MAX)), 'NULL')
END
ELSE ''
END + CASE WHEN is_output = 1 THEN ' OUTPUT'
WHEN is_readonly = 1 THEN ' READONLY'
ELSE ''
END
END AS ProcParam) p2
WHERE pr.OBJECT_ID = m.OBJECT_ID) p2
CROSS APPLY (SELECT 'WITH EXECUTE AS ' + ISNULL(QUOTENAME(dp.name), 'CALLER') AS ExecAs,
'EXTERNAL NAME ' + QUOTENAME(a.NAME COLLATE SQL_Latin1_General_CP1_CI_AS) + '.' + QUOTENAME(m.assembly_class COLLATE SQL_Latin1_General_CP1_CI_AS)
+ ISNULL('.' + QUOTENAME(m.assembly_method COLLATE SQL_Latin1_General_CP1_CI_AS), '') AS ExtName) ea
CROSS APPLY (SELECT CASE WHEN ot.CreateDefinition = 'AGGREGATE' THEN ot.CreateDefinition + ' ' + FQN + '
' + ISNULL(FuncInParams, '()') + '
' + FuncOutParam + '
' + ExtName WHEN ot.CreateDefinition = 'FUNCTION' THEN ot.CreateDefinition + ' ' + FQN + '
' + ISNULL(FuncInParams, '()') + '
' + CASE WHEN o.type = 'FT' THEN ReturnTable
ELSE FuncOutParam
END + '
' + ExecAs + CASE WHEN m.null_on_null_input = 1 THEN ', RETURNS NULL ON NULL INPUT'
ELSE ''
END + '
AS
' + ExtName WHEN ot.CreateDefinition = 'PROCEDURE' THEN ot.CreateDefinition + ' ' + FQN + '
' + ProcParams + '
' + ExecAs + '
AS
' + ExtName
END AS Def) dd
CROSS APPLY (SELECT dd.Def AS ProcDefinition) fd
WHERE a.is_user_defined = 1
AND o.is_ms_shipped = 0), '')
OPTION (RECOMPILE)
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
@FilePath AS FilePath,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
IF @PrintScript = 1
EXEC Util.dbo.PrintLargeText
@SQL
END
GO
EXEC sys.sp_ms_marksystemobject
sp_objects
GO