USE master
GO
IF OBJECT_ID('sp_MetadataViewsCreate') IS NULL EXEC ('CREATE PROCEDURE sp_MetadataViewsCreate AS SELECT 1 AS ID')
GO
ALTER PROCEDURE sp_MetadataViewsCreate
@SQL VARCHAR(MAX) = NULL OUTPUT,
@PrintSQL BIT = 0,
@ExecSQL BIT = 1,
@IncludeDBList VARCHAR(MAX) = NULL,
@ExcludeDBList VARCHAR(MAX) = NULL,
@CreateMetadataUser BIT = 1,
@CreateDBAAllViews BIT = 1,
@DropAndRecreateMetadata BIT = 0
AS
SET NOCOUNT ON
--#region Scripts
;
WITH Incl
AS (SELECT LTRIM(RTRIM(field)) AS DBName
FROM Util.dbo.ParseDelimited(@IncludeDBList, ',')),
Excl
AS (SELECT LTRIM(RTRIM(field)) AS DBName
FROM Util.dbo.ParseDelimited(@ExcludeDBList, ',')),
dbs
AS (SELECT d.name AS DatabaseName
FROM sys.databases d (NOLOCK)
WHERE is_read_only = 0
AND STATE = 0
AND (NOT EXISTS ( SELECT *
FROM Incl )
OR EXISTS ( SELECT *
FROM Incl
WHERE DBName = d.name ))
AND (NOT EXISTS ( SELECT *
FROM Excl )
OR NOT EXISTS ( SELECT *
FROM Excl
WHERE DBName = d.name ))
AND NOT EXISTS ( SELECT *
FROM sys.dm_tran_locks t1 (NOLOCK)
WHERE resource_type = 'database'
AND request_status = 'grant'
AND request_mode = 'u'
AND t1.resource_database_id = d.database_id )),
Functions
AS (SELECT Name,
SQL
FROM ( VALUES ( 'GetIndexPhysicalStats', 'CREATE FUNCTION Metadata.GetIndexPhysicalStats (@database_id smallint,
@object_id INT,
@index_id INT,
@partition_number INT,
@mode sysname)
RETURNS @result TABLE ([database_id] SMALLINT NULL,
[object_id] INT NULL,
[index_id] INT NULL,
[partition_number] INT NULL,
[index_type_desc] NVARCHAR(60) NULL,
[alloc_unit_type_desc] NVARCHAR(60) NULL,
[index_depth] TINYINT NULL,
[index_level] TINYINT NULL,
[avg_fragmentation_in_percent] FLOAT NULL,
[fragment_count] BIGINT NULL,
[avg_fragment_size_in_pages] FLOAT NULL,
[page_count] BIGINT NULL,
[avg_page_space_used_in_percent] FLOAT NULL,
[record_count] BIGINT NULL,
[ghost_record_count] BIGINT NULL,
[version_ghost_record_count] BIGINT NULL,
[min_record_size_in_bytes] INT NULL,
[max_record_size_in_bytes] INT NULL,
[avg_record_size_in_bytes] FLOAT NULL,
[forwarded_record_count] BIGINT NULL,
[compressed_page_count] BIGINT NULL)
BEGIN
INSERT INTO @result
SELECT *
FROM sys.dm_db_index_physical_stats(@database_id, @object_id, @index_id, @partition_number, @mode)
RETURN
END'), ( 'GetIOVirtualFileStats', 'CREATE FUNCTION Metadata.GetIOVirtualFileStats(
@database_id INT = NULL,
@file_id INT = NULL)
RETURNS @result TABLE(
database_id SMALLINT NOT NULL,
FILE_ID SMALLINT NOT NULL,
sample_ms INT NOT NULL,
num_of_reads BIGINT NOT NULL,
num_of_bytes_read BIGINT NOT NULL,
io_stall_read_ms BIGINT NOT NULL,
num_of_writes BIGINT NOT NULL,
num_of_bytes_written BIGINT NOT NULL,
io_stall_write_ms BIGINT NOT NULL,
io_stall BIGINT NOT NULL,
size_on_disk_bytes BIGINT NOT NULL,
file_handle VARBINARY(8)NOT NULL)
BEGIN
INSERT INTO @result(
database_id,
FILE_ID,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes,
file_handle)
SELECT
database_id,
FILE_ID,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes,
file_handle
FROM sys.dm_io_virtual_file_stats(@database_id, @file_id)
RETURN
END') ) d (Name, SQL)),
VIEWS
AS (SELECT Name,
SQL
FROM ( VALUES ( 'Tables', 'SELECT QUOTENAME(s.NAME) + ''.'' + QUOTENAME(t.name) AS FQN,
s.name AS SchemaName,
t.name AS TableName,
cc.ColCnt,
pc.PKColCnt,
p.[Rows],
p.EmptyPart,
p.PartMaxRows,
p.Compression,
i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.name AS DataSpace,
ds.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DataSpaceDesc,
HasIdent,
TriggerCount,
FKRefCount,
FKCount,
HasClustInd,
NonClustIndCnt,
HasPrimaryKey,
HasUniqueKey,
UniqueIndexCnt,
t.lob_data_space_id AS LobDataSpaceId,
dl.name COLLATE SQL_Latin1_General_CP1_CI_AS AS LobDataSpace,
t.filestream_data_space_id AS FilestreamDataSpaceId,
df.name COLLATE SQL_Latin1_General_CP1_CI_AS AS FilestreamData,
t.max_column_id_used AS MaxColumnIdUsed,
t.lock_on_bulk_load AS LockOnBulkLoad,
t.uses_ansi_nulls AS UsesAnsiNulls,
t.is_replicated AS IsReplicated,
t.has_replication_filter AS HasReplicationFilter,
t.is_merge_published AS IsMergePublished,
t.is_sync_tran_subscribed AS IsSyncTranSubscribed,
t.has_unchecked_assembly_data AS HasUncheckedAssemblyData,
t.text_in_row_limit AS TextInRowLimit,
t.large_value_types_out_of_row AS LargeValueTypesOutOfRow,
t.is_tracked_by_cdc AS IsTrackedByCdc,
t.lock_escalation AS LockEscalation,
t.lock_escalation_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS LockEscalationDesc,
t.create_date AS CreateDate,
t.modify_date AS ModifyDate,
t.object_id AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.schema_id = t.schema_id
LEFT OUTER JOIN sys.data_spaces dl (NOLOCK) ON dl.data_space_id = t.lob_data_space_id
LEFT OUTER JOIN sys.data_spaces df (NOLOCK) ON df.data_space_id = t.filestream_data_space_id
INNER JOIN sys.indexes i (NOLOCK) ON i.object_id = t.object_id
AND i.index_id <= 1
LEFT OUTER JOIN sys.data_spaces ds (NOLOCK) ON ds.data_space_id = i.data_space_id
CROSS APPLY (SELECT COUNT(*) AS ColCnt,
SUM(CASE WHEN c.is_identity = 1 THEN 1
ELSE 0
END) AS HasIdent
FROM sys.columns c (NOLOCK)
WHERE c.object_id = t.object_id) cc
CROSS APPLY (SELECT COUNT (*) AS TriggerCount FROM sys.triggers tr (NOLOCK) WHERE tr.parent_id = t.OBJECT_ID) tc
CROSS APPLY (SELECT SUM(CASE WHEN referenced_object_id = ol.OBJECT_ID THEN 1
ELSE 0
END) AS FKRefCount,
SUM(CASE WHEN fk.parent_object_id = ol.OBJECT_ID THEN 1
ELSE 0
END) AS FKCount
FROM sys.foreign_keys fk (NOLOCK)
INNER JOIN sys.tables ol ON referenced_object_id = ol.OBJECT_ID
OR fk.parent_object_id = ol.OBJECT_ID
WHERE ol.object_id = t.object_id) fks
CROSS APPLY (SELECT SUM(CASE WHEN index_id > 1 THEN 1
ELSE 0
END) AS NonClustIndCnt,
COUNT(DISTINCT CASE WHEN index_id = 1 THEN index_id
END) AS HasClustInd,
COUNT(DISTINCT CASE WHEN is_primary_key = 1 THEN 1
END) HasPrimaryKey,
COUNT(DISTINCT CASE WHEN is_unique_constraint = 1 THEN is_unique_constraint
END) HasUniqueKey,
SUM(CASE WHEN is_unique = 1 THEN 1
ELSE 0
END) UniqueIndexCnt
FROM sys.indexes i (NOLOCK)
WHERE i.object_id = t.OBJECT_ID) ix
CROSS APPLY (SELECT COUNT(*) PKColCnt
FROM sys.indexes i (NOLOCK)
INNER JOIN sys.index_columns ic (NOLOCK) ON ic.index_id = i.index_id
AND ic.object_id = i.object_id
WHERE i.object_id = t.object_id
AND i.is_primary_key = 1) pc
CROSS APPLY (SELECT COUNT(*) AS PartCnt,
SUM(p.rows) AS [Rows],
SUM(CASE WHEN p.ROWS = 0 THEN 1
ELSE 0
END) AS EmptyPart,
MAX(p.rows) AS PartMaxRows,
Util.dbo.StringConcat(DISTINCT p.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS, '', '') AS Compression
FROM sys.partitions p (NOLOCK)
WHERE p.object_id = i.object_id
AND p.index_id = i.index_id) p
WHERE t.is_ms_shipped = 0'), ( 'Columns', 'SELECT o.Type,
ot.TypeName,
QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
c.name AS ColumnName,
ct.ColumnType AS ColumnDef,
c.column_id AS ColumnId,
ic.key_ordinal AS PKOrdinal,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity,
c.max_length AS MaxLength,
c.is_computed AS IsComputed,
dc.definition AS DefaultDefinition,
ck.definition AS CheckDefinition,
cc.DEFINITION AS ComputeDefinition,
o.object_id AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN sys.columns c (NOLOCK) ON c.object_id = o.object_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
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()
LEFT OUTER JOIN sys.indexes i (NOLOCK) ON i.object_id = o.object_id
AND i.is_primary_key = 1
LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.object_id = o.object_id
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.default_constraints dc (NOLOCK) ON dc.parent_object_id = o.object_id
AND dc.parent_column_id = c.column_id
LEFT OUTER JOIN sys.check_constraints ck (NOLOCK) ON ck.parent_object_id = o.object_id
AND ck.parent_column_id = c.column_id
LEFT OUTER JOIN sys.computed_columns cc (NOLOCK) ON cc.object_id = o.object_id
AND cc.column_id = c.column_id
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.precision, c.scale, c.collation_name, db.collation_name) ct
WHERE o.is_ms_shipped = 0')
, ( 'TableColumns', 'SELECT QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
c.name AS ColumnName,
ct.ColumnType AS ColumnDef,
c.column_id AS ColumnId,
ic.key_ordinal AS PKOrdinal,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity,
c.max_length AS MaxLength,
c.is_computed AS IsComputed,
dc.definition AS DefaultDefinition,
ck.definition AS CheckDefinition,
cc.DEFINITION AS ComputeDefinition,
o.object_id AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN sys.columns c (NOLOCK) ON c.object_id = o.object_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
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()
LEFT OUTER JOIN sys.indexes i (NOLOCK) ON i.object_id = o.object_id
AND i.is_primary_key = 1
LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.object_id = o.object_id
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.default_constraints dc (NOLOCK) ON dc.parent_object_id = o.object_id
AND dc.parent_column_id = c.column_id
LEFT OUTER JOIN sys.check_constraints ck (NOLOCK) ON ck.parent_object_id = o.object_id
AND ck.parent_column_id = c.column_id
LEFT OUTER JOIN sys.computed_columns cc (NOLOCK) ON cc.object_id = o.object_id
AND cc.column_id = c.column_id
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.precision, c.scale, c.collation_name, db.collation_name) ct
WHERE o.is_ms_shipped = 0')
, ( 'ViewColumns', 'SELECT
QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
c.name AS ColumnName,
ct.ColumnType AS ColumnDef,
c.column_id AS ColumnId,
c.is_nullable AS IsNullable,
c.max_length AS MaxLength,
o.object_id AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.views o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN sys.columns c (NOLOCK) ON c.object_id = o.object_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
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 o.is_ms_shipped = 0')
, ( 'FunctionColumns', 'SELECT
QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
c.name AS ColumnName,
ct.ColumnType AS ColumnDef,
c.column_id AS ColumnId,
c.is_nullable AS IsNullable,
c.max_length AS MaxLength,
o.object_id AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN sys.columns c (NOLOCK) ON c.object_id = o.object_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
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 o.is_ms_shipped = 0
AND o.type IN (''IF'',''TF'')')
,
( 'Defaults', 'SELECT FQN,
s.NAME AS SchemaName,
t.NAME AS TableName,
c.NAME AS ColumnName,
ct.ColumnType AS ColumnDef,
d.NAME AS DefaultName,
d.DEFINITION AS [Definition],
d.is_system_named AS IsSystemNamed,
CreateScript,
DropScript,
d.create_date AS CreateDate,
d.modify_date AS ModifyDate,
t.object_id AS ObjectId
FROM sys.schemas s(NOLOCK)
INNER JOIN sys.tables t(NOLOCK) ON s.schema_id = t.schema_id
INNER JOIN sys.default_constraints d(NOLOCK) ON D.parent_object_id = t.OBJECT_ID
CROSS APPLY (
SELECT QUOTENAME(s.NAME) + ''.'' + QUOTENAME(T.NAME) AS FQN
) kk
INNER JOIN sys.columns c(NOLOCK) ON c.OBJECT_ID = T.OBJECT_ID
AND c.column_id = D.parent_column_id
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
CROSS APPLY (
SELECT ''IF OBJECT_ID('''''' + QUOTENAME(s.NAME) + ''.'' + QUOTENAME(d.NAME) + '''''') IS NOT NULL ALTER TABLE '' + FQN + '' DROP CONSTRAINT '' + QUOTENAME(d.NAME) AS DropScript,
''ALTER TABLE '' + FQN + '' ADD CONSTRAINT '' + QUOTENAME(d.NAME) + '' DEFAULT '' + DEFINITION + '' FOR '' + QUOTENAME(c.NAME) AS CreateScript
) k2
WHERE t.is_ms_shipped = 0'), ( 'Dependencies', 'SELECT
o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS RingType,
so.TypeName AS RingTypeName,
QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS RingFQN,
s.Name AS RingSchema,
o.name AS RingName,
oo.type COLLATE SQL_Latin1_General_CP1_CI_AS AS RedType,
soo.TypeName AS RedTypeName,
QUOTENAME(ss.NAME) + ''.'' + QUOTENAME(oo.name) AS RedFQN,
ss.name AS RedSchema,
oo.name AS RedName,
cc.name AS RedColumn,
a.is_selected AS IsSelected,
a.is_updated AS IsUpdated,
a.is_select_all AS IsSelectAll,
a.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassDesc,
o.object_id AS RingObjectId,
oo.object_id AS RedObjectId
FROM sys.sql_dependencies a (NOLOCK)
LEFT OUTER JOIN sys.objects o (NOLOCK) ON o.object_id = a.object_id
LEFT OUTER JOIN sys.schemas s (NOLOCK) ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.objects oo (NOLOCK) ON oo.object_id = a.referenced_major_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes so ON so.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN Util.dbo.SystemObjectTypes soo ON soo.Type = oo.type COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.schemas ss (NOLOCK) ON ss.schema_id = oo.schema_id
LEFT OUTER JOIN sys.columns cc (NOLOCK) ON cc.object_id = oo.object_id
AND cc.column_id = a.referenced_minor_id'),
( 'ForeignKeys', 'WITH AllTables
AS (SELECT s.SCHEMA_ID,
s.NAME AS SchemaName,
o.OBJECT_ID,
o.NAME AS ObjectName,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS TYPE,
QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.NAME) AS FQN
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables o (NOLOCK) ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped = 0),
FK1
AS (SELECT pt.FQN AS RingFQN,
pt.SchemaName AS RingSchema,
pt.ObjectName AS RingTable,
QUOTENAME(pt.SchemaName) + ''.'' + QUOTENAME(fk.NAME) AS FKFQN,
fk.NAME AS FKName,
rt.FQN AS RedFQN,
rt.SchemaName AS RedSchema,
rt.ObjectName AS RedTable,
''f'' + CAST(DENSE_RANK() OVER (PARTITION BY fk.parent_object_id ORDER BY rt.SchemaName, rt.ObjectName, fk.NAME) AS VARCHAR) AS ALias,
fk.is_disabled AS IsDisabled,
fk.is_not_trusted AS IsNotTrusted,
fk.is_system_named AS IsSystemNamed,
fk.is_not_for_replication AS IsNotForReplication,
delete_referential_action AS DeleteAction,
delete_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DeleteActionDesc,
update_referential_action AS UpdateAction,
update_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS UpdateActionDesc,
fk.create_date AS CreateDate,
fk.modify_date AS ModifyDate,
fk.OBJECT_ID AS FKObjectId,
pt.OBJECT_ID AS RingObjectId,
rt.OBJECT_ID AS RedObjectId
FROM sys.foreign_keys fk (NOLOCK)
INNER JOIN AllTables pt ON fk.parent_object_id = pt.OBJECT_ID
INNER JOIN AllTables rt ON fk.referenced_object_id = rt.OBJECT_ID)
SELECT RingFQN,
RingSchema,
RingTable,
RingColumns,
FKFQN,
FKName,
ColumnCnt,
RedFQN,
RedSchema,
RedTable,
RedColumns,
IsDisabled,
IsNotTrusted,
IsSystemNamed,
IsNotForReplication,
DeleteAction,
DeleteActionDesc,
UpdateAction,
UpdateActionDesc,
CreateDate,
ModifyDate,
FKObjectId,
RingObjectId,
RedObjectId,
''LEFT OUTER JOIN '' + QUOTENAME(RedSchema) + ''.'' + QUOTENAME(RedTable) + '' '' + Alias + '' ON '' + REPLACE(JoinScript, ''#Alias#'', Alias) AS JoinScript,
CreateScript,
DropScript
FROM FK1
CROSS APPLY (SELECT ColumnCnt,
RingColumns,
RedColumns,
JoinScript,
''IF OBJECT_ID('''''' + FKFQN + '''''') IS NOT NULL ALTER TABLE '' + RingFQN + '' DROP CONSTRAINT '' + QUOTENAME(FKName) AS DropScript,
''ALTER TABLE '' + RingFQN + CASE WHEN IsDisabled = 1 THEN '' WITH NOCHECK''
ELSE ''''
END + '' ADD CONSTRAINT '' + QUOTENAME(FKName) + '' FOREIGN KEY ('' + RingColumns + '') REFERENCES '' + RedFQN
+ '' ('' + RedColumns + '')'' + CASE WHEN DeleteAction > 0 THEN '' ON DELETE '' + DeleteActionDesc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
ELSE ''''
END + CASE WHEN UpdateAction > 0 THEN '' ON UPDATE '' + UpdateActionDesc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
ELSE ''''
END + CASE WHEN IsNotForReplication = 1 THEN '' NOT FOR REPLICATION''
ELSE ''''
END + CASE WHEN IsDisabled = 1 THEN ''
ALTER TABLE '' + RingFQN + '' NOCHECK CONSTRAINT '' + QUOTENAME(FKName) + ''
'' ELSE ''''
END AS CreateScript
FROM (SELECT COUNT(*) AS ColumnCnt,
Util.dbo.StringConcat(QUOTENAME(RingColumn), '', '') AS RingColumns,
Util.dbo.StringConcat(QUOTENAME(RedColumn), '', '') AS RedColumns,
+Util.dbo.StringConcat(''pr.'' + QUOTENAME(RingColumn) + '' = '' + ''#Alias#'' + ''.'' + QUOTENAME(RedColumn), '' AND '') AS JoinScript
FROM (SELECT TOP 999999
pc.NAME AS RingColumn,
rc.NAME AS RedColumn
FROM sys.foreign_key_columns fc (NOLOCK)
INNER JOIN sys.columns pc (NOLOCK) ON pc.OBJECT_ID = RingObjectId
AND fc.parent_column_id = pc.column_id
INNER JOIN sys.columns rc (NOLOCK) ON rc.OBJECT_ID = RedObjectId
AND fc.referenced_column_id = rc.column_id
WHERE fc.constraint_object_id = FKObjectId
ORDER BY fc.constraint_column_id) k) fc) k'), ( 'Indexes', 'WITH IndexColumns
AS (SELECT o.OBJECT_ID AS ObjectId,
CASE o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN ''U'' THEN ''TABLE''
WHEN ''V'' THEN ''VIEW''
ELSE o.type COLLATE SQL_Latin1_General_CP1_CI_AS
END AS ObjectType,
QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name ObjectName,
CASE WHEN ix.is_primary_key = 1
OR ix.is_unique_constraint = 1 THEN ix.name
ELSE ix.name
END AS IndexName,
ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
CASE ds.type
WHEN ''FG'' THEN ''FILEGROUP''
WHEN ''PS'' THEN ''PARTITION SCHEME''
WHEN ''FD'' THEN ''FILESTREAM''
ELSE ds.TYPE
END AS DataspaceType,
ds.name AS Dataspace,
ix.index_id,
ix.is_unique,
ix.is_primary_key,
ix.ignore_dup_key,
ix.is_unique_constraint,
ix.is_disabled,
ix.filter_definition,
ix.fill_factor,
ix.is_padded,
ix.allow_row_locks,
ix.allow_page_locks,
pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DATA_COMPRESSION,
KeyCnt,
IncludeCnt,
PartitionCnt,
KeyColumns,
IncludeColumns,
PartitionedCol,
CASE WHEN ds.type = ''FG'' THEN pr.ROWS
END AS [Rows]
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN sys.indexes AS ix (NOLOCK) ON o.object_id = ix.object_id
INNER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = ix.data_space_id
OUTER APPLY (SELECT pf.fanout AS PartitionCnt
FROM sys.partition_functions pf (NOLOCK)
INNER JOIN sys.partition_schemes ps (NOLOCK) ON ps.function_id = pf.function_id
WHERE ps.data_space_id = ix.data_space_id) pc
INNER JOIN sys.partitions AS pr (NOLOCK) ON pr.object_id = ix.object_id
AND pr.index_id = ix.index_id
CROSS APPLY (SELECT SUM(CASE WHEN key_ordinal > 0 THEN 1
ELSE 0
END) AS KeyCnt,
SUM(CASE WHEN is_included_column > 0 THEN 1
ELSE 0
END) AS IncludeCnt,
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 PartitionedCol
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 (NOLOCK)
INNER JOIN sys.columns AS cl (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
WHERE ix.index_id > 0
AND o.type IN (''U'', ''V'')
AND pr.partition_number = 1
AND o.is_ms_shipped = 0)
SELECT ObjectType,
FQN,
SchemaName,
ObjectName,
IndexName,
IndexType,
Rows,
index_id AS IndexId,
is_unique AS [Unique],
is_primary_key AS PK,
is_unique_constraint AS UnqC,
ignore_dup_key AS IgnoreDup,
is_disabled AS [Disabled],
KeyCnt,
KeyColumns,
IncludeCnt,
PartitionCnt,
IncludeColumns,
filter_definition AS Filter,
PartitionedCol,
data_compression AS Compression,
DataspaceType,
Dataspace,
fill_factor AS [FillFactor],
is_padded AS PadIndex,
allow_row_locks AS AllowRowLocks,
allow_page_locks AS AllowPageLocks,
ObjectId
FROM IndexColumns ic'), ( 'PartitionValues', 'SELECT pf.name AS FunctionName,
ps.name AS SchemeName,
pf.function_id AS Functionid,
ps.data_space_id AS DataSpaceId,
rv.Value,
PartitionFilter,
rv.boundary_id AS BoundaryId,
pf.type COLLATE SQL_Latin1_General_CP1_CI_AS AS Type,
pf.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeDesc,
pf.boundary_value_on_right AS RangeRight
FROM sys.data_spaces (NOLOCK) ds
INNER JOIN sys.partition_schemes ps (NOLOCK) ON ds.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions pf (NOLOCK) ON pf.function_id = ps.function_id
INNER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = pf.function_id
INNER JOIN sys.partition_parameters pp (NOLOCK) ON pp.function_id = pf.function_id
INNER JOIN sys.types t (NOLOCK) ON pp.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.partition_range_values rv2 (NOLOCK) ON rv2.function_id = pf.function_id
AND rv2.boundary_id = rv.boundary_id - 1
AND rv.boundary_id > 1
CROSS APPLY (SELECT CASE WHEN t.NAME LIKE ''%char%'' THEN '''''''' + REPLACE(CAST(rv.Value AS VARCHAR), '''''''', '''''''''''') + ''''''''
WHEN t.NAME LIKE ''%date%''
OR t.NAME LIKE ''%time%'' THEN '''''''' + CAST(rv.Value AS VARCHAR) + ''''''''
ELSE CAST(rv.Value AS VARCHAR)
END AS CharValue,
CASE WHEN t.NAME LIKE ''%char%'' THEN '''''''' + REPLACE(CAST(rv2.Value AS VARCHAR), '''''''', '''''''''''') + ''''''''
WHEN t.NAME LIKE ''%date%''
OR t.NAME LIKE ''%time%'' THEN '''''''' + CAST(rv2.Value AS VARCHAR) + ''''''''
ELSE CAST(rv2.Value AS VARCHAR)
END AS PrevCharValue) cv
CROSS APPLY (SELECT CASE WHEN pf.boundary_value_on_right = 0
THEN CASE WHEN rv.boundary_id > 1 THEN ''@PartCol > '' + PrevCharValue + '' AND @PartCol <= '' + CharValue
ELSE ''@PartCol <= '' + CharValue
END
WHEN pf.boundary_value_on_right = 1
THEN CASE WHEN rv.boundary_id > 1 THEN ''@PartCol >= '' + PrevCharValue + '' AND @PartCol < '' + CharValue
ELSE ''@PartCol < '' + CharValue
END
END AS PartitionFilter) rf
WHERE pp.parameter_id = 1
AND rv.parameter_id = 1'), ( 'IndexDetail', 'WITH Part
AS (SELECT ps.data_space_id,
pf.boundary_value_on_right,
pf.fanout AS PartitionCnt,
rv.boundary_id AS PartitionNumber,
T.NAME AS TypeName,
rv.[Value],
rv2.VALUE AS PrevValue
FROM sys.partition_functions pf (NOLOCK)
INNER JOIN sys.partition_schemes ps (NOLOCK) ON ps.function_id = pf.function_id
INNER JOIN sys.partition_parameters pp (NOLOCK) ON pp.function_id = pf.function_id
INNER JOIN sys.types t (NOLOCK) ON pp.user_type_id = t.user_type_id
INNER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values rv2 (NOLOCK) ON rv2.function_id = pf.function_id
AND rv2.boundary_id = rv.boundary_id - 1
AND rv.boundary_id > 1
AND rv2.parameter_id = 1
WHERE rv.parameter_id = 1
AND pp.parameter_id = 1)
SELECT QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
i.name AS IndexName,
i.index_id AS IndexId,
i.is_primary_key AS PK,
i.is_unique AS [Unique],
i.is_unique_constraint AS UnqC,
i.ignore_dup_key AS IgnoreDup,
i.is_disabled AS Disabled,
p.partition_number AS [Partition],
p7.PartitionCnt,
pt.PrevValue AS PartPreVal,
p7.PartitionValue AS PartVal,
g.PartitionColumn,
rf.PartitionFilter,
i.filter_definition AS IndexFilter,
g.KeyCnt,
g.KeyColumns,
g.IncludeCnt,
g.IncludeColumns,
p.Rows,
CAST(pss.used_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS UsedMB,
CAST(pss.reserved_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS ReservedMB,
CASE WHEN pss.reserved_page_count > 0 THEN CAST(pss.used_page_count * 100.0 / pss.reserved_page_count AS NUMERIC(6, 2))
END AS [Used%],
CASE WHEN p.Rows > 0 THEN pss.used_page_count * 8 * 1024 / p.ROWS
END AS BytesPerRow,
CAST(pss.in_row_data_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS InRowDataMB,
CAST(pss.in_row_used_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS InRowUsedMB,
CAST(pss.in_row_reserved_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS InRowReservedMB,
CAST(pss.lob_used_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS LobUsedMB,
CAST(pss.lob_reserved_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS LobReservedMB,
CAST(pss.row_overflow_used_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS RowOverflowUsedMB,
CAST(pss.row_overflow_reserved_page_count * 8.0 / 1024 AS NUMERIC(12, 2)) AS RowOverflowReservedMB,
p7.BoundaryOnRight,
i.fill_factor AS [FillFactor],
i.is_padded AS PadIndex,
i.allow_row_locks AS AllowRowLocks,
i.allow_page_locks AS AllowPageLocks,
p.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS Compression,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectType,
ds.name AS DataSpace,
CASE ds.type
WHEN ''FG'' THEN ''FILEGROUP''
WHEN ''PS'' THEN ''PARTITION SCHEME''
WHEN ''FD'' THEN ''FILESTREAM''
ELSE ds.TYPE
END AS DataspaceDesc,
ds.type COLLATE SQL_Latin1_General_CP1_CI_AS AS DataSpaceType,
i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
i.data_space_id AS DataSpaceId,
p.hobt_id AS HobtId,
o.object_id AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN sys.indexes i (NOLOCK) ON i.object_id = o.object_id
LEFT OUTER JOIN sys.data_spaces ds (NOLOCK) ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partitions p (NOLOCK) ON p.object_id = o.object_id
AND p.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_partition_stats (NOLOCK) pss ON pss.OBJECT_ID = o.OBJECT_ID
AND pss.partition_id = p.partition_id
AND pss.index_id = i.index_id
AND p.partition_number = pss.partition_number
OUTER APPLY (SELECT Util.dbo.StringConcat(CASE WHEN partition_ordinal = 1 THEN ColumnName
END, '','') AS PartitionColumn,
Util.dbo.StringConcat(CASE WHEN key_ordinal > 0 THEN ColumnName + CASE WHEN is_descending_key = 1 THEN '' DESC''
ELSE ''''
END
END, '','') AS KeyColumns,
SUM(CASE WHEN key_ordinal > 0 THEN 1
ELSE 0
END) AS KeyCnt,
Util.dbo.StringConcat(CASE WHEN is_included_column > 0 THEN ColumnName
END, '','') AS IncludeColumns,
SUM(CASE WHEN is_included_column > 0 THEN 1
ELSE 0
END) AS IncludeCnt
FROM (SELECT TOP 999999
ic.partition_ordinal,
ic.key_ordinal,
ic.is_included_column,
ic.is_descending_key,
c.name AS ColumnName
FROM sys.columns c (NOLOCK)
INNER JOIN sys.index_columns ic (NOLOCK) ON ic.column_id = c.column_id
WHERE c.OBJECT_ID = o.OBJECT_ID
AND ic.object_id = o.object_id
AND ic.index_id = i.index_id
ORDER BY ic.key_ordinal,
index_column_id) k) g
LEFT OUTER JOIN Part pt ON ds.TYPE = ''PS''
AND pt.data_space_id = i.data_space_id
AND pt.PartitionNumber = p.partition_number
OUTER APPLY (SELECT TOP 1
ps.data_space_id,
pf.boundary_value_on_right,
pf.fanout AS PartitionCnt,
rv.boundary_id AS PartitionNumber,
T.NAME AS TypeName,
rv.[Value]
FROM sys.partition_functions pf (NOLOCK)
INNER JOIN sys.partition_schemes ps (NOLOCK) ON ps.function_id = pf.function_id
INNER JOIN sys.partition_parameters pp (NOLOCK) ON pp.function_id = pf.function_id
INNER JOIN sys.types t (NOLOCK) ON pp.user_type_id = t.user_type_id
INNER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = pf.function_id
WHERE rv.parameter_id = 1
AND pp.parameter_id = 1
AND ds.TYPE = ''PS''
AND ps.data_space_id = i.data_space_id
ORDER BY rv.boundary_id DESC) rv
OUTER APPLY (SELECT ISNULL(pt.TypeName, rv.TypeName) AS TypeName,
ISNULL(pt.VALUE, rv.VALUE) AS PartitionValue,
ISNULL(pt.PartitionCnt, rv.PartitionCnt) AS PartitionCnt,
ISNULL(pt.boundary_value_on_right, rv.boundary_value_on_right) AS BoundaryOnRight
WHERE ds.TYPE = ''PS'') p7
OUTER APPLY (SELECT CASE WHEN p7.TypeName LIKE ''%char%'' THEN '''''''' + REPLACE(CAST(PartitionValue AS VARCHAR), '''''''', '''''''''''') + ''''''''
WHEN p7.TypeName LIKE ''%date%''
OR p7.TypeName LIKE ''%time%'' THEN '''''''' + CAST(PartitionValue AS VARCHAR) + ''''''''
ELSE CAST(PartitionValue AS VARCHAR)
END AS CharValue,
CASE WHEN p7.TypeName LIKE ''%char%'' THEN '''''''' + REPLACE(CAST(pt.PrevValue AS VARCHAR), '''''''', '''''''''''') + ''''''''
WHEN p7.TypeName LIKE ''%date%''
OR p7.TypeName LIKE ''%time%'' THEN '''''''' + CAST(pt.PrevValue AS VARCHAR) + ''''''''
ELSE CAST(pt.PrevValue AS VARCHAR)
END AS PrevCharValue
WHERE ds.TYPE = ''PS'') pt2
OUTER APPLY (SELECT CASE WHEN p7.BoundaryOnRight = 0
THEN CASE WHEN p.partition_number = p7.PartitionCnt THEN g.PartitionColumn + '' > '' + pt2.CharValue
WHEN p.partition_number > 1
THEN g.PartitionColumn + '' > '' + pt2.PrevCharValue + '' AND '' + g.PartitionColumn + '' <= '' + pt2.CharValue
ELSE g.PartitionColumn + '' <= '' + pt2.CharValue
END
WHEN p7.BoundaryOnRight = 1
THEN CASE WHEN p.partition_number = p7.PartitionCnt THEN g.PartitionColumn + '' >= '' + pt2.CharValue
WHEN p.partition_number > 1
THEN g.PartitionColumn + '' >= '' + pt2.PrevCharValue + '' AND '' + g.PartitionColumn + '' < '' + pt2.CharValue
ELSE g.PartitionColumn + '' < '' + pt2.CharValue
END
END AS PartitionFilter
WHERE ds.TYPE = ''PS'') rf
WHERE o.is_ms_shipped = 0'), ( 'StoredProcedureParams', 'SELECT QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
p.ParameterId,
p.ParameterName,
p.TypeName AS DataType,
p.ColumnType,
p.IsOutput,
p.IsReadOnly,
p.HasDefault,
p.DefaultValue,
p.ParsedDefaultValue,
o.object_id AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
OUTER APPLY (SELECT p.ParameterId,
p.ParameterName,
p.ColumnType,
pr.HasDefault,
pr.Value AS DefaultValue,
p.TypeName,
p.collation_name,
p.PRECISION,
pr.ParsedValue AS ParsedDefaultValue,
p.IsOutput,
p.IsReadOnly
FROM (SELECT p.parameter_id AS ParameterId,
p.NAME AS ParameterName,
ct.ColumnType,
t.NAME AS TypeName,
t.collation_name,
t.PRECISION,
p.is_output AS IsOutput,
p.is_readonly AS IsReadOnly
FROM sys.parameters p (NOLOCK)
LEFT OUTER JOIN sys.types t (NOLOCK) ON t.user_type_id = p.user_type_id
CROSS APPLY Util.dbo.GetColumnType(t.name, p.max_length, p.precision, p.scale, db.collation_name, db.collation_name) ct
WHERE p.object_id = o.object_id) p
LEFT OUTER JOIN (SELECT pr.*
FROM sys.all_sql_modules m (NOLOCK)
OUTER APPLY Util.dbo.ParseSPDefaults(m.uses_quoted_identifier, m.definition) pr
WHERE m.object_id = o.object_id) pr ON p.ParameterId = pr.ParameterId) P
WHERE o.type = ''P''
AND NOT (s.name = ''dbo''
AND (o.name LIKE ''sys%''
OR o.name LIKE ''s%diagram%''))
AND s.name NOT IN (''cdc'')
AND o.is_ms_shipped = 0'), ( 'CheckConstraints', 'SELECT FQN,
s.NAME AS SchemaName,
t.NAME AS TableName,
CheckFQN,
c.NAME AS CheckConstraint,
b.ColumnCount AS ColCnt,
b.ColumnList,
c.DEFINITION AS DEFINITION,
c.is_disabled AS IsDisabled,
c.is_not_trusted AS IsNotTrusted,
c.is_system_named AS IsSystemNamed,
CreateScript,
DropScript,
c.create_date AS CreateDate,
c.modify_date AS ModifyDate,
t.object_id AS ObjectId
FROM sys.schemas AS s(NOLOCK)
INNER JOIN sys.tables AS t(NOLOCK) ON t.schema_id = s.schema_id
INNER JOIN sys.check_constraints AS c(NOLOCK) ON t.object_id = c.parent_object_id
CROSS APPLY (
SELECT QUOTENAME(s.NAME) + ''.'' + QUOTENAME(t.NAME) AS FQN,
QUOTENAME(s.NAME) + ''.'' + QUOTENAME(c.NAME) AS CheckFQN
) AS d
CROSS APPLY (
SELECT COUNT(*) AS ColumnCount,
Util.dbo.StringConcat(DISTINCT cl.NAME, '', '') AS ColumnList
FROM sys.columns AS cl(NOLOCK)
WHERE cl.object_id = t.object_id
AND CHARINDEX(''['' + NAME + '']'', c.DEFINITION, 1) > 0
) AS b
CROSS APPLY (
SELECT ''IF OBJECT_ID('''''' + CheckFQN + '''''') IS NOT NULL ALTER TABLE '' + FQN + '' DROP CONSTRAINT '' + QUOTENAME(c.NAME) AS DropScript,
''ALTER TABLE '' + FQN + '' '' + CASE
WHEN is_disabled = 1
THEN ''WITH NOCHECK ''
ELSE ''''
END + ''ADD CONSTRAINT '' + QUOTENAME(c.NAME) + '' CHECK '' + DEFINITION + CASE
WHEN is_disabled = 1
THEN ''
ALTER TABLE '' + FQN + '' NOCHECK CONSTRAINT '' + QUOTENAME(c.NAME)
ELSE ''''
END AS CreateScript
) AS kk'), ( 'Objects', 'WITH OBJECTS
AS (SELECT s.name AS SchemaName,
o.name AS ObjectName,
QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS FQN,
o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
ot.CreateDefinition AS TypeName,
dp.name AS Principal,
o.create_date AS CreateDate,
o.modify_date AS ModifyDate,
o.is_published AS IsPublished,
o.is_schema_published AS IsSchemaPublished,
o.object_id AS ObjectId,
o.parent_object_id AS ParentObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.database_principals dp (NOLOCK) ON o.principal_id IS NOT NULL
AND dp.principal_id = o.principal_id
WHERE is_ms_shipped = 0)
SELECT c.FQN,
c.SchemaName,
c.ObjectName,
c.Type,
c.TypeName,
c.Principal,
c.CreateDate,
c.ModifyDate,
c.IsPublished,
c.IsSchemaPublished,
p.SchemaName AS ParentSchemaName,
p.ObjectName AS ParentObjectName,
p.Type AS ParentType,
p.TypeName AS ParentTypeName,
p.Principal AS ParentPrincipal,
p.CreateDate AS ParentCreateDate,
p.ModifyDate AS ParentModifyDate,
p.IsPublished AS ParentIsPublished,
p.IsSchemaPublished AS ParentIsSchemaPublished,
c.ObjectId,
c.ParentObjectId
FROM OBJECTS c
LEFT OUTER JOIN OBJECTS p ON c.ParentObjectId <> 0
AND p.ObjectId = c.ParentObjectId
'),
( 'ExpressionDependencies', 'SELECT otd.CreateDefinition AS RingTypeName,
ed.referencing_class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS RingClassDesc,
ISNULL(QUOTENAME(sd.NAME) + ''.'', '''') + QUOTENAME(ISNULL(od.name, tr.name)) AS RingFQN,
sd.name AS RingSchema,
ISNULL(od.name, tr.name) AS RingName,
otg.CreateDefinition AS RedTypeName,
ed.referenced_class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS RedClassDesc,
ISNULL(QUOTENAME(ed.referenced_server_name) + ''.'', '''') + ISNULL(QUOTENAME(ed.referenced_database_name) + ''.'', '''') + QUOTENAME(ed.referenced_schema_name)
+ ''.'' + QUOTENAME(ed.referenced_entity_name) AS RedFQN,
ed.referenced_server_name AS RedServer,
ed.referenced_database_name AS RedDatabase,
ed.referenced_schema_name AS RedSchema,
ed.referenced_entity_name AS RedName,
ed.is_schema_bound_reference AS IsSchemaBound,
ed.is_caller_dependent AS IsCallerDependent,
ed.is_ambiguous AS IsAmbiguous,
od.type COLLATE SQL_Latin1_General_CP1_CI_AS AS RingType,
ed.referencing_id AS RingId,
ed.referencing_class AS RingClass,
og.type COLLATE SQL_Latin1_General_CP1_CI_AS AS RedType,
ed.referenced_id AS RedId,
ed.referenced_class AS RedClass
FROM sys.sql_expression_dependencies ed (NOLOCK)
LEFT OUTER JOIN sys.objects od (NOLOCK) ON od.object_id = ed.referencing_id
LEFT OUTER JOIN sys.triggers tr (NOLOCK) ON tr.object_id = ed.referencing_id
AND od.object_id IS NULL
LEFT OUTER JOIN sys.schemas sd (NOLOCK) ON sd.schema_id = od.schema_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes otd ON ISNULL(od.type, tr.type) = otd.Type COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.objects og (NOLOCK) ON og.object_id = ed.referenced_id
LEFT OUTER JOIN sys.schemas sg (NOLOCK) ON sg.schema_id = og.schema_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes otg ON og.type = otg.Type COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE ed.referenced_minor_id = 0
AND ed.referencing_minor_id = 0'),
( 'ExpressionDependencyColumns', 'SELECT otd.CreateDefinition AS RingTypeName,
ed.referencing_class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS RingClassDesc,
ISNULL(QUOTENAME(sd.NAME) + ''.'', '''') + QUOTENAME(ISNULL(od.name, tr.name)) AS RingFQN,
sd.name AS RingSchema,
ISNULL(od.name, tr.name) AS RingName,
cd.name AS RingColumn,
otg.CreateDefinition AS RedTypeName,
ed.referenced_class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS RedClassDesc,
ISNULL(QUOTENAME(ed.referenced_server_name) + ''.'', '''') + ISNULL(QUOTENAME(ed.referenced_database_name) + ''.'', '''') + QUOTENAME(ed.referenced_schema_name)
+ ''.'' + QUOTENAME(ed.referenced_entity_name) AS RedFQN,
ed.referenced_server_name AS RedServer,
ed.referenced_database_name AS RedDatabase,
ed.referenced_schema_name AS RedSchema,
ed.referenced_entity_name AS RedName,
cg.name AS RedColumn,
ed.is_schema_bound_reference AS IsSchemaBound,
ed.is_caller_dependent AS IsCallerDependent,
ed.is_ambiguous AS IsAmbiguous,
od.type COLLATE SQL_Latin1_General_CP1_CI_AS AS RingType,
ed.referencing_id AS RingId,
ed.referencing_minor_id AS RingMinorId,
ed.referencing_class AS RingClass,
og.type COLLATE SQL_Latin1_General_CP1_CI_AS AS RedType,
ed.referenced_id AS RedId,
ed.referenced_minor_id AS RedMinorId,
ed.referenced_class AS RedClass
FROM sys.sql_expression_dependencies ed (NOLOCK)
LEFT OUTER JOIN sys.objects od (NOLOCK) ON od.object_id = ed.referencing_id
LEFT OUTER JOIN sys.triggers tr (NOLOCK) ON tr.object_id = ed.referencing_id
AND od.object_id IS NULL
LEFT OUTER JOIN sys.schemas sd (NOLOCK) ON sd.schema_id = od.schema_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes otd ON ISNULL(od.type, tr.type) = otd.Type COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.columns cd (NOLOCK) ON ed.referencing_class = 1
AND cd.object_id = ed.referencing_id
AND cd.column_id = ed.referencing_minor_id
AND ed.referencing_minor_id > 0
LEFT OUTER JOIN sys.objects og (NOLOCK) ON og.object_id = ed.referenced_id
LEFT OUTER JOIN sys.schemas sg (NOLOCK) ON sg.schema_id = og.schema_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes otg ON og.type = otg.Type COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN sys.columns cg (NOLOCK) ON ed.referenced_class = 1
AND cg.object_id = ed.referenced_id
AND cg.column_id = ed.referenced_minor_id
AND ed.referenced_minor_id > 0
WHERE ed.referenced_minor_id > 0
OR ed.referencing_minor_id > 0'),
( 'Principals', 'SELECT dp.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS UserName,
dp.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS Type,
dp.Type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeName,
dp.is_fixed_role AS IsFixedRole,
dp.create_date AS CreateDate,
CASE WHEN DoNotScript = 0
THEN ''IF NOT EXISTS (SELECT * FROM sys.database_principals (NOLOCK) WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS = N'''''' + dp.name + '''''')''
END COLLATE SQL_Latin1_General_CP1_CI_AS AS NotExistsSQL,
CASE WHEN DoNotScript = 0
THEN CASE dp.[Type]
WHEN ''G''
THEN ''CREATE USER ['' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''] FOR LOGIN ['' + sp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']''
+ ISNULL(''WITH DEFAULT_SCHEMA = ['' + dp.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS + '']'', '''')
WHEN ''U''
THEN ''CREATE USER ['' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''] FOR LOGIN ['' + sp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']''
+ ISNULL(''WITH DEFAULT_SCHEMA = ['' + dp.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS + '']'', '''')
WHEN ''S''
THEN ''CREATE USER ['' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''] '' + ISNULL(''FOR LOGIN ['' + sp.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ '']'', ''WITHOUT LOGIN'')
+ ISNULL('' WITH DEFAULT_SCHEMA=['' + dp.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS + '']'', '''')
WHEN ''R''
THEN ''CREATE ROLE ['' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']'' + ISNULL('' AUTHORIZATION ['' + op.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ '']'', '''')
END
END COLLATE SQL_Latin1_General_CP1_CI_AS AS CreateSQL,
CASE WHEN DoNotScript = 0
THEN ''IF EXISTS (SELECT * FROM sys.database_principals (NOLOCK) WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS = N'''''' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ '''''')''
END COLLATE SQL_Latin1_General_CP1_CI_AS AS ExistsSQL,
CASE WHEN DoNotScript = 0 THEN CASE dp.[Type]
WHEN ''G'' THEN ''DROP USER ['' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']''
WHEN ''U'' THEN ''DROP USER ['' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']''
WHEN ''S'' THEN ''DROP USER ['' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']''
WHEN ''R'' THEN ''DROP ROLE ['' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']''
END
END COLLATE SQL_Latin1_General_CP1_CI_AS AS DropSQL,
ISNULL(sl.isntname, 0) AS IsNtName,
ISNULL(sl.isntgroup, 0) AS IsNtGroup,
ISNULL(sl.isntuser, 0) AS IsNtUser,
ISNULL(sl.sysadmin, 0) AS IsSysAdmin,
ISNULL(sl.securityadmin, 0) AS IsSecurityAdmin,
ISNULL(sl.serveradmin, 0) AS IsServerAdmin,
ISNULL(sl.setupadmin, 0) AS IsSetupAdmin,
ISNULL(sl.processadmin, 0) AS IsProcessAdmin,
ISNULL(sl.diskadmin, 0) AS IsDiskAdmin,
ISNULL(sl.dbcreator, 0) AS IsDbCreator,
ISNULL(sl.bulkadmin, 0) AS IsBulkAdmin,
dp.principal_id AS PrincipalId,
op.owning_principal_id AS PrincipalOwnerId
FROM sys.database_principals dp (NOLOCK)
LEFT OUTER JOIN sys.server_principals sp (NOLOCK) ON sp.sid = dp.sid
LEFT OUTER JOIN sys.database_principals op (NOLOCK) ON dp.owning_principal_id = op.principal_id
LEFT OUTER JOIN master.sys.syslogins sl (NOLOCK) ON sl.SID = dp.sid
CROSS APPLY (SELECT CASE WHEN dp.is_fixed_role = 1
OR dp.name COLLATE SQL_Latin1_General_CP1_CI_AS IN (''public'', ''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'') THEN 1
ELSE 0
END AS DoNotScript) d
WHERE dp.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''U'', ''G'', ''S'', ''R'')
AND dp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')'),
( 'RoleMembersRecursive', 'WITH RoleMembers
AS (SELECT rp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS RoleName,
mp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
a.role_principal_id AS RoleId,
a.member_principal_id AS MemberId
FROM sys.database_role_members a (NOLOCK)
INNER JOIN sys.database_principals rp (NOLOCK) ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals mp (NOLOCK) ON mp.principal_id = a.member_principal_id
WHERE mp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')
AND mp.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''U'', ''G'', ''S'', ''R'')),
users
AS (SELECT Name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
principal_id AS MemberId,
type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS UserType,
create_date AS CreateDate,
is_fixed_role AS IsFixedRole
FROM sys.database_principals (NOLOCK)
WHERE TYPE IN (''S'', ''U'', ''G'', ''R'')
AND NAME NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')),
MemberLinks
AS (SELECT MemberName AS RoleName,
MemberId AS RoleId,
MemberName,
MemberId,
UserType,
CreateDate,
IsFixedRole,
0 AS [Level],
CAST('''' AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CI_AS AS HierarchyTree
FROM users
UNION ALL
SELECT r.RoleName,
r.RoleId,
l.MemberName,
l.MemberId,
l.UserType,
NULL AS CreateDate,
NULL AS IsFixedRole,
l.[Level] + 1 AS [Level],
CAST(l.HierarchyTree COLLATE SQL_Latin1_General_CP1_CI_AS + CASE WHEN l.HierarchyTree COLLATE SQL_Latin1_General_CP1_CI_AS = '''' THEN ''''
ELSE '',''
END + r.RoleName COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CI_AS AS HierarchyTree
FROM MemberLinks l
INNER JOIN RoleMembers r ON r.MemberId = l.RoleId)
SELECT [MemberName],
[RoleName],
[UserType],
[Level],
HierarchyTree,
IsFixedRole,
CreateDate,
[MemberId],
[RoleId]
FROM MemberLinks'),
( 'RoleMembers', 'SELECT rp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS RoleName,
mp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
mp.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberType,
mp.Type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberTypeName,
''EXEC sp_addrolemember N'''''' + rp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''''', N'''''' + mp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''''''' AS AddSQL,
''EXEC sp_droprolemember N'''''' + rp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''''', N'''''' + mp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''''''' AS DropSQL,
mp.principal_id AS MemberPrincipalId,
rp.principal_id AS RolePrincipalId
FROM sys.database_role_members a (NOLOCK)
INNER JOIN sys.database_principals rp (NOLOCK) ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals mp (NOLOCK) ON mp.principal_id = a.member_principal_id
WHERE mp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')
AND mp.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''U'', ''G'', ''S'', ''R'')'),
( 'Permissions', 'WITH Perm
AS (SELECT d.Class,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
p.name COLLATE SQL_Latin1_General_CP1_CI_AS AS UserName,
s.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectName,
Permission,
StateDesc AS StateDesc,
StateDesc + '' '' + Permission + '' ON SCHEMA::['' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''] TO ['' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ''];'' AS GrantSQL,
''REVOKE '' + Permission + '' ON SCHEMA::['' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''] TO ['' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ''];'' AS RevokeSQL,
p.principal_id
FROM sys.database_permissions d (NOLOCK)
INNER JOIN sys.database_principals p (NOLOCK) ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.schemas s (NOLOCK) ON s.schema_id = d.major_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission) ca
WHERE d.class = 3 /*''SCHEMA''*/
AND p.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''U'', ''G'', ''S'', ''R'')
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')
UNION ALL
SELECT d.Class,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
p.name COLLATE SQL_Latin1_General_CP1_CI_AS AS UserName,
DB_NAME() AS ObjectName,
Permission,
StateDesc AS StateDesc,
StateDesc + '' '' + Permission + '' TO ['' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''];'' AS GrantSQL,
''REVOKE '' + Permission + '' TO ['' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''];'' AS RevokeSQL,
p.principal_id
FROM sys.database_permissions d (NOLOCK)
INNER JOIN sys.database_principals p (NOLOCK) ON d.grantee_principal_id = p.principal_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission) ca
WHERE d.class = 0 /*DATABASE*/
AND p.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''U'', ''G'', ''S'', ''R'')
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')
UNION ALL
SELECT d.Class,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
p.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS UserName,
''['' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''].['' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']'' + ISNULL('' (['' + co.NAME COLLATE SQL_Latin1_General_CP1_CI_AS
+ '']) '', '''') AS ObjectName,
Permission,
StateDesc AS StateDesc,
StateDesc + '' '' + Permission + '' ON ['' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''].['' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ''] '' + ISNULL(''(['' + co.NAME COLLATE SQL_Latin1_General_CP1_CI_AS + '']) '', '''') + ''TO ['' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ''];'' AS GrantSQL,
''REVOKE '' + Permission + '' ON ['' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''].['' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ''] '' + ISNULL(''(['' + co.NAME COLLATE SQL_Latin1_General_CP1_CI_AS + '']) '', '''') + ''TO ['' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ''];'' AS RevokeSQL,
p.principal_id
FROM sys.database_permissions d (NOLOCK)
INNER JOIN sys.database_principals p (NOLOCK) ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.objects o (NOLOCK) ON o.object_id = d.major_id
INNER JOIN sys.schemas s (NOLOCK) ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.columns co ON co.object_id = o.object_id
AND co.column_id = d.minor_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission) ca
WHERE d.class = 1 /*OBJECT OR COLUMN*/
AND p.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''U'', ''G'', ''S'', ''R'')
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc''))
SELECT Class,
ClassName,
UserName,
ObjectName,
Permission,
StateDesc,
GrantSQL,
RevokeSQL,
principal_id AS PrincipalId
FROM Perm'), ( 'IndexPartitionDetails', 'SELECT s.name AS SchemaName,
o.name ObjectName,
o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectType,
ix.name AS IndexName,
ix.index_id AS IndexId,
ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.type AS DataspaceType,
ds.name AS DataspaceName,
ix.is_unique AS IsUnique,
ix.is_primary_key AS IsPrimaryKey,
ix.ignore_dup_key AS IgnoreDupKey,
ix.is_unique_constraint AS IsUniqueConstraint,
ix.is_disabled AS IsDisabled,
ix.has_filter AS HasFilter,
pr.partition_number AS PartitionNumber,
pr.DATA_COMPRESSION AS DataCompression,
pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DataCompressionDesc,
pr.Rows,
o.OBJECT_ID AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN sys.indexes AS ix (NOLOCK) ON o.object_id = ix.object_id
INNER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = ix.data_space_id
INNER JOIN sys.partitions AS pr (NOLOCK) ON pr.object_id = ix.object_id
AND pr.index_id = ix.index_id
WHERE o.is_ms_shipped = 0
AND o.type IN (''U'',''V'')'),
( 'ProcedureScripts', 'WITH IndexColumns
AS (SELECT pc.object_id,
s.NAME AS 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 ix.ignore_dup_key = 1 THEN '', IGNORE_DUP_KEY = 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 sys.views (NOLOCK) pc
INNER JOIN sys.schemas s (NOLOCK) ON s.schema_id = pc.schema_id
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),
indexfinal
AS (SELECT object_id,
Util.dbo.StringConcat(Script, ''
GO
'') AS IndexScript
FROM Indexes i
GROUP BY object_id)
--#endregion
--#region Objects create
SELECT schema_id AS ObjectId,
''SCHEMA'' AS [Type],
QUOTENAME(s.NAME) AS FQN,
s.name AS SchemaName,
s.NAME AS ObjectName,
''SCHEMA'' AS CreateDefinition,
''CREATE SCHEMA ['' + s.name + ''] AUTHORIZATION ['' + d.name + '']'' AS [Definition],
''ALTER AUTHORIZATION ON SCHEMA::['' + s.name + ''] TO ['' + d.name + '']'' AS AlterScript,
NULL AS IndexScript,
NULL AS AnsiSQL,
''DROP SCHEMA ['' + s.name + '']'' AS DropScript,
CAST(NULL AS VARCHAR(256)) AS AssemblyName,
CAST(NULL AS DATETIME) AS CreateDate,
CAST(NULL AS DATETIME) AS ModifyDate
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.database_principals d (NOLOCK) ON s.principal_id = d.principal_id
UNION ALL
SELECT o.object_id AS ObjectId,
o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
ot.CreateDefinition,
m.Definition,
NULL AS AlterScript,
di.IndexScript,
''SET ANSI_NULLS '' + CASE WHEN m.uses_ansi_nulls = 1 THEN ''ON''
ELSE ''OFF''
END + ''
SET QUOTED_IDENTIFIER '' + CASE WHEN uses_quoted_identifier = 1 THEN ''ON''
ELSE ''OFF''
END AS AnsiSQL,
''DROP '' + ot.CreateDefinition COLLATE SQL_Latin1_General_CP1_CI_AS + '' '' + QUOTENAME(S.NAME COLLATE SQL_Latin1_General_CP1_CI_AS) + ''.''
+ QUOTENAME(o.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS DropScript,
CAST(NULL AS VARCHAR(256)) AS AssemblyName,
o.create_date AS CreateDate,
o.modify_date AS ModifyDate
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.schema_id = s.schema_id
INNER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.type COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN sys.all_sql_modules m (NOLOCK) ON m.object_id = o.object_id
LEFT OUTER JOIN indexfinal di ON m.is_schema_bound = 1
AND di.object_id = o.object_id
WHERE ot.CreateDefinition IN (''PROCEDURE'', ''VIEW'', ''TRIGGER'', ''FUNCTION'')
AND o.is_ms_shipped = 0
AND s.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''cdc'', ''sys'')
AND NOT (s.name COLLATE SQL_Latin1_General_CP1_CI_AS = ''dbo''
AND o.NAME COLLATE SQL_Latin1_General_CP1_CI_AS LIKE ''s%diagram%'')
UNION ALL
SELECT m.object_id AS ObjectId,
o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
QUOTENAME(s.NAME) + ''.'' + QUOTENAME(o.name) AS FQN,
s.name AS SchemaName,
o.name AS ObjectName,
ot.CreateDefinition,
fd.Definition,
fd.AlterScript,
CAST(NULL AS VARCHAR(MAX)) AS IndexScript,
CAST(NULL AS VARCHAR(MAX)) AS AnsiSQL,
''DROP '' + ot.CreateDefinition + '' '' + FQN AS DropScript,
a.name COLLATE SQL_Latin1_General_CP1_CI_AS AS AssemblyName,
o.create_date AS CreateDate,
o.modify_date AS ModifyDate
FROM sys.assembly_modules m (NOLOCK)
INNER JOIN SYS.assemblies a (NOLOCK) ON a.assembly_id = m.assembly_id
INNER JOIN sys.objects o (NOLOCK) ON o.object_id = m.object_id
INNER JOIN sys.schemas s (NOLOCK) ON s.schema_id = o.schema_id
CROSS APPLY (SELECT QUOTENAME (s.name) + ''.'' + QUOTENAME (o.name) AS FQN) f
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 ''CREATE '' + dd.Def AS Definition,
CASE WHEN CreateDefinition = ''AGGREGATE'' THEN ''DROP AGGREGATE '' + FQN + ''
EXEC('''''' + ''CREATE '' + dd.Def + '''''')''
ELSE ''ALTER '' + dd.Def
END AS AlterScript) fd
WHERE a.is_user_defined = 1
AND o.is_ms_shipped = 0')
, ( 'Assemblies', 'WITH DepOrd
AS (SELECT assembly_id,
1 AS OrderId
FROM sys.assembly_references a (NOLOCK)
WHERE NOT EXISTS ( SELECT *
FROM sys.assembly_references b (NOLOCK)
WHERE b.referenced_assembly_id = a.assembly_id )
UNION ALL
SELECT referenced_assembly_id AS assembly_id,
S.OrderId + 1 AS OrderId
FROM sys.assembly_references a (NOLOCK)
INNER JOIN DepOrd s ON s.assembly_id = a.assembly_id),
s2
AS (SELECT assembly_id,
MAX(orderID) AS OrderId
FROM DepOrd s
GROUP BY assembly_id),
OrdFinal
AS (SELECT MAX(s2.OrderId) OVER (PARTITION BY (SELECT 0)) + 1 - s2.OrderId AS OrderId,
a.name,
a.assembly_id
FROM s2
INNER JOIN sys.assemblies a (NOLOCK) ON a.assembly_id = s2.assembly_id
WHERE a.is_user_defined = 1
UNION
SELECT 1 AS OrderId,
name,
assembly_id
FROM sys.assemblies a (NOLOCK)
WHERE a.is_user_defined = 1
AND NOT EXISTS ( SELECT *
FROM sys.assembly_references r (NOLOCK)
WHERE r.referenced_assembly_id = a.assembly_id
OR r.assembly_id = a.assembly_id ))
SELECT s.name AS AssemblyName,
d.name AS Principal,
CASE s.permission_set
WHEN 1 THEN ''SAFE''
WHEN 2 THEN ''EXTERNAL_ACCESS''
WHEN 3 THEN ''UNSAFE ''
ELSE ''N/A''
END AS PermSQL,
s.PERMISSION_SET AS PermissionSet,
s.PERMISSION_SET_DESC COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionSetDesc,
s.clr_name COLLATE SQL_Latin1_General_CP1_CI_AS AS CLRName,
ISNULL(f.OrderId, 1) AS CreateOrderId,
af.Content,
s.create_date AS CreateDate,
S.modify_date AS ModifyDate
FROM sys.assemblies s (NOLOCK)
LEFT OUTER JOIN OrdFinal f ON f.assembly_id = s.assembly_id
INNER JOIN sys.database_principals d (NOLOCK) ON s.principal_id = d.principal_id
INNER JOIN sys.assembly_files af (NOLOCK) ON af.assembly_id = s.assembly_id
CROSS APPLY (SELECT CONVERT(VARCHAR(MAX), af.content, 1) AS CharContent,
CASE s.permission_set
WHEN 1 THEN ''SAFE''
WHEN 2 THEN ''EXTERNAL_ACCESS''
WHEN 3 THEN ''UNSAFE ''
ELSE ''N/A''
END AS Perm) z
WHERE s.is_user_defined = 1
AND af.file_id = 1'),
( 'TableScripts', 'WITH Tables
AS (SELECT t.object_id,
QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) AS FQN,
s.name AS SchemaName,
t.name AS TableName,
t.lock_escalation_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS lock_escalation_desc,
t.uses_ansi_nulls,
t.create_date AS CreateDate,
t.modify_date AS ModifyDate
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0),
--#region TableScript
TableScript
AS (SELECT t.object_id,
t.FQN,
t.SchemaName,
t.TableName,
t.TableName AS ObjectName,
''U'' AS TYPE,
DropScript,
CreateScript,
CreateDate,
ModifyDate
FROM Tables t
INNER JOIN sys.indexes AS ix WITH (NOLOCK) ON t.object_id = ix.OBJECT_ID
AND ix.index_id <= 1
INNER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON ds.data_space_id = ix.data_space_id
LEFT OUTER JOIN sys.partitions AS pr WITH (NOLOCK) ON pr.object_id = ix.object_id
AND pr.index_id = ix.index_id
AND pr.partition_number = 1
LEFT OUTER JOIN sys.index_columns AS ic WITH (NOLOCK) ON ic.object_id = ix.object_id
AND ic.index_id = ix.index_id
AND ic.partition_ordinal = 1
LEFT OUTER JOIN sys.columns AS pc WITH (NOLOCK) ON pc.object_id = ic.object_id
AND pc.column_id = ic.column_id
CROSS APPLY (SELECT '')
'' + CASE ds.type
WHEN ''FG'' THEN ''ON '' + QUOTENAME(ds.NAME)
WHEN ''PS'' THEN ''ON '' + QUOTENAME(ds.NAME) + ''('' + QUOTENAME(pc.NAME) + '')''
WHEN ''FD'' THEN ''''
ELSE ''''
END + CASE pr.data_compression
WHEN 0 THEN ''''
WHEN 1 THEN '' WITH (DATA_COMPRESSION = ROW)''
WHEN 2 THEN '' WITH (DATA_COMPRESSION = PAGE)''
ELSE ''''
END + CASE WHEN lock_escalation_desc <> ''TABLE'' THEN ''
ALTER TABLE '' + FQN + '' SET (LOCK_ESCALATION = '' + lock_escalation_desc + '')
'' ELSE ''
''
END AS AfterScript) ss
CROSS APPLY (SELECT Util.dbo.StringConcat(ColumnDef, '',
'') AS ColumnDef
FROM (SELECT QUOTENAME(co.NAME) + '' '' + CASE WHEN cc.column_id IS NOT NULL
THEN ''AS '' + cc.definition + CASE WHEN cc.is_persisted = 1
THEN '' PERSISTED''
+ CASE WHEN co.is_nullable = 0
THEN '' NOT NULL''
ELSE '' NULL''
END
ELSE ''''
END
ELSE CASE WHEN ts.name IN (''char'', ''varchar'')
THEN ts.name + ''('' + CASE WHEN co.max_length = -1 THEN ''MAX''
ELSE CAST(co.max_length AS VARCHAR)
END + '')''
+ CASE WHEN co.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS <> db.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS
THEN '' COLLATE '' + co.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE ''''
END
WHEN ts.name IN (''nchar'', ''nvarchar'')
THEN ts.name + ''('' + CASE WHEN co.max_length = -1 THEN ''MAX''
ELSE CAST(co.max_length / 2 AS VARCHAR)
END + '')''
+ CASE WHEN co.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS <> db.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS
THEN '' COLLATE '' + co.collation_name COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE ''''
END
WHEN ts.name IN (''binary'', ''varbinary'')
THEN ts.name + ''('' + CASE WHEN co.max_length = -1 THEN ''MAX''
ELSE CAST(co.max_length AS VARCHAR)
END + '')''
WHEN ts.name IN (''bigint'', ''int'', ''smallint'', ''tinyint'') THEN ts.name
WHEN ts.name IN (''datetime2'', ''time'', ''datetimeoffset'')
THEN ts.name + ''('' + CAST(co.scale AS VARCHAR) + '')''
WHEN ts.name IN (''numeric'', ''decimal'')
THEN ts.name + ''('' + CAST(co.precision AS VARCHAR) + '', ''
+ CAST(co.scale AS VARCHAR) + '')''
ELSE ts.name
END + CASE WHEN co.is_identity = 1 THEN '' IDENTITY''
ELSE ''''
END + '' '' + CASE WHEN co.is_nullable = 1 THEN ''NULL''
ELSE ''NOT NULL''
END
END AS ColumnDef
FROM sys.columns AS co WITH (NOLOCK)
INNER JOIN sys.types AS ts WITH (NOLOCK) ON ts.user_type_id = co.user_type_id
INNER JOIN sys.databases AS db WITH (NOLOCK) ON db.database_id = DB_ID()
LEFT OUTER JOIN sys.indexes ix (NOLOCK) ON ix.object_id = co.object_id
AND ix.is_primary_key = 1
LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
AND co.column_id = ic.column_id
LEFT OUTER JOIN sys.computed_columns AS cc (NOLOCK) ON co.is_computed = 1
AND co.object_id = cc.object_id
AND co.column_id = cc.column_id
WHERE co.object_id = t.OBJECT_ID) k) k
CROSS APPLY (SELECT ''IF OBJECT_ID('''''' + FQN + '''''') IS NOT NULL DROP TABLE '' + FQN AS DropScript,
''CREATE TABLE '' + FQN + '' (
'' + ColumnDef + AfterScript AS CreateScript) cs)--#endregion
, Defaults
AS (SELECT t.object_id,
QUOTENAME(t.SchemaName) + ''.'' + QUOTENAME(dc.name) AS FQN,
t.SchemaName,
t.TableName,
dc.name AS ObjectName,
''D'' AS TYPE,
DropScript,
CreateScript,
dc.create_date AS CreateDate,
dc.modify_date AS ModifyDate
FROM Tables t
INNER JOIN sys.default_constraints AS dc (NOLOCK) ON dc.parent_object_id = t.object_id
INNER JOIN sys.columns c (NOLOCK) ON c.object_id = t.object_id
AND dc.parent_column_id = c.column_id
CROSS APPLY (SELECT ''IF OBJECT_ID('''''' + QUOTENAME(t.SchemaName) + ''.'' + QUOTENAME(dc.name) + '''''') IS NOT NULL ALTER TABLE '' + FQN
+ '' DROP CONSTRAINT '' + QUOTENAME(dc.name) AS DropScript,
''ALTER TABLE '' + FQN + '' ADD CONSTRAINT '' + QUOTENAME(dc.name) + '' DEFAULT '' + definition + '' FOR '' + QUOTENAME(c.name) AS CreateScript) kk),
--#region Indexes
Indexes
AS (SELECT t.object_id,
t.SchemaName,
t.TableName,
CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1 THEN QUOTENAME(t.SchemaName) + ''.'' + QUOTENAME(ix.name)
ELSE QUOTENAME(ix.name)
END AS FQN,
ix.name AS ObjectName,
ix.index_id AS IndexId,
CASE WHEN is_primary_key = 1 THEN ''PK''
WHEN is_unique_constraint = 1 THEN ''UQ''
ELSE ''I''
END AS TYPE,
DropScript,
CreateScript,
t.CreateDate,
t.ModifyDate
FROM Tables t
INNER JOIN sys.indexes AS ix WITH (NOLOCK) ON t.object_id = ix.object_id
AND ix.index_id > 0
CROSS APPLY (SELECT QUOTENAME (ix.name) AS IndexName) f
LEFT OUTER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON ds.data_space_id = ix.data_space_id
LEFT OUTER JOIN sys.partitions AS pr WITH (NOLOCK) ON pr.object_id = ix.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 99999999
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
CROSS APPLY (SELECT ISNULL('' WITH ('' + NULLIF(LTRIM(RTRIM(SUBSTRING(ISNULL('', '' + dc.DataCompression, '''') + '', SORT_IN_TEMPDB = ON''
+ ISNULL(CASE WHEN ix.ignore_dup_key = 1 THEN '', IGNORE_DUP_KEY = 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) io
CROSS APPLY (SELECT CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1
THEN ''IF OBJECT_ID('''''' + QUOTENAME(SchemaName) + ''.'' + IndexName + '''''') IS NOT NULL ALTER TABLE '' + FQN
+ '' DROP CONSTRAINT '' + IndexName
ELSE ''IF EXISTS(SELECT * FROM sys.indexes (NOLOCK) WHERE object_id = OBJECT_ID('''''' + FQN + '''''') AND name = ''''''
+ ix.name + '''''') DROP INDEX '' + IndexName + '' ON '' + FQN
END AS DropScript,
CASE WHEN has_filter = 1 THEN ''SET ANSI_NULLS '' + CASE WHEN uses_ansi_nulls = 1 THEN ''ON''
ELSE ''OFF''
END + ''
'' ELSE ''''
END + CASE WHEN is_primary_key = 1
OR is_unique_constraint = 1
THEN ''ALTER TABLE '' + FQN + '' ADD CONSTRAINT '' + IndexName + '' ''
+ CASE WHEN is_primary_key = 1 THEN ''PRIMARY KEY ''
ELSE ''UNIQUE ''
END + ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + '' ('' + KeyColumns + '')'' + IndexOptions + '' ''
+ CASE ds.type COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN ''FG'' THEN ''ON '' + QUOTENAME(ds.name COLLATE SQL_Latin1_General_CP1_CI_AS)
WHEN ''PS''
THEN ''ON '' + QUOTENAME(ds.name COLLATE SQL_Latin1_General_CP1_CI_AS) + ''('' + PartitionedColumn + '')''
WHEN ''FD'' THEN ''''
ELSE ''''
END
ELSE ''CREATE '' + CASE WHEN is_unique = 1 THEN ''UNIQUE ''
ELSE ''''
END + ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + '' INDEX '' + IndexName + '' ON '' + FQN
+ '' ('' + KeyColumns + '')'' + ISNULL('' INCLUDE ('' + IncludeColumns + '')'', '''') + ISNULL('' WHERE ''
+ filter_definition, '''')
+ IndexOptions + '' '' + CASE ds.type COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN ''FG'' THEN ''ON '' + QUOTENAME(ds.name COLLATE SQL_Latin1_General_CP1_CI_AS) + ''''
WHEN ''PS''
THEN ''ON '' + QUOTENAME(ds.name COLLATE SQL_Latin1_General_CP1_CI_AS) + ''(''
+ PartitionedColumn + '')''
WHEN ''FD'' THEN ''''
ELSE ''''
END
END AS CreateScript) cs),
--#endregion
--#region Check Constraints
Checks
AS (SELECT t.object_id,
QUOTENAME(t.SchemaName) + ''.'' + QUOTENAME(ck.name) AS FQN,
t.SchemaName,
t.TableName,
ck.name AS ObjectName,
''C'' AS TYPE,
DropScript,
CreateScript,
ck.create_date AS CreateDate,
ck.modify_date AS ModifyDate
FROM Tables t
INNER JOIN sys.check_constraints AS ck (NOLOCK) ON ck.parent_object_id = t.object_id
CROSS APPLY (SELECT ''IF OBJECT_ID('''''' + QUOTENAME(t.SchemaName) + ''.'' + QUOTENAME(ck.name) + '''''') IS NOT NULL ALTER TABLE '' + FQN
+ '' DROP CONSTRAINT '' + QUOTENAME(ck.name) AS DropScript,
''ALTER TABLE '' + FQN + '' '' + CASE WHEN is_disabled = 1 THEN ''WITH NOCHECK ''
ELSE ''''
END + ''ADD CONSTRAINT '' + QUOTENAME(ck.name) + '' CHECK '' + definition
+ CASE WHEN is_disabled = 1 THEN ''
ALTER TABLE '' + FQN + '' NOCHECK CONSTRAINT '' + QUOTENAME(ck.name)
ELSE ''''
END AS CreateScript) kk),
--#endregion
--#region ForeignKeys
ForeignKeys
AS (SELECT t.object_id,
QUOTENAME(t.SchemaName) + ''.'' + QUOTENAME(fk.name) AS FQN,
t.SchemaName,
t.TableName,
fk.name AS ObjectName,
''F'' AS TYPE,
DropScript,
CreateScript,
fk.create_date AS CreateDate,
fk.modify_date AS ModifyDate
FROM Tables t
INNER JOIN sys.foreign_keys AS fk (NOLOCK) ON fk.parent_object_id = t.object_id
INNER JOIN sys.tables AS rt (NOLOCK) ON rt.object_id = fk.referenced_object_id
INNER JOIN sys.schemas AS rs (NOLOCK) ON rs.schema_id = rt.schema_id
CROSS APPLY (SELECT Util.dbo.StringConcat(QUOTENAME(ParentColumn), '', '') AS ParentColumns,
Util.dbo.StringConcat(QUOTENAME(ReferencedColumn), '', '') AS ReferencedColumns
FROM (SELECT TOP 999999
pc.name AS ParentColumn,
rc.name AS ReferencedColumn
FROM sys.foreign_key_columns fc (NOLOCK)
INNER JOIN sys.columns pc (NOLOCK) ON pc.OBJECT_ID = fk.parent_object_id
AND fc.parent_column_id = pc.column_id
INNER JOIN sys.columns rc (NOLOCK) ON rc.object_id = fk.referenced_object_id
AND fc.referenced_column_id = rc.column_id
WHERE fc.constraint_object_id = fk.object_id
ORDER BY fc.constraint_column_id) K) fc
CROSS APPLY (SELECT ''IF OBJECT_ID('''''' + QUOTENAME(t.SchemaName) + ''.'' + QUOTENAME(fk.name) + '''''') IS NOT NULL ALTER TABLE '' + FQN
+ '' DROP CONSTRAINT '' + QUOTENAME(fk.name) AS DropScript,
''ALTER TABLE '' + FQN + CASE WHEN is_disabled = 1 THEN '' WITH NOCHECK''
ELSE ''''
END + '' ADD CONSTRAINT '' + QUOTENAME(fk.name) + '' FOREIGN KEY ('' + ParentColumns + '') REFERENCES ''
+ QUOTENAME(rs.name) + ''.'' + QUOTENAME(rt.name) + '' ('' + ReferencedColumns + '')''
+ CASE WHEN delete_referential_action > 0
THEN '' ON DELETE '' + delete_referential_action_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
ELSE ''''
END + CASE WHEN update_referential_action > 0
THEN '' ON UPDATE '' + update_referential_action_desc COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
ELSE ''''
END + CASE WHEN is_not_for_replication = 1 THEN '' NOT FOR REPLICATION''
ELSE ''''
END + CASE WHEN is_disabled = 1 THEN ''
ALTER TABLE '' + FQN + '' NOCHECK CONSTRAINT '' + QUOTENAME(fk.name) + ''
'' ELSE ''''
END AS CreateScript) sc)
--#endregion
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
0 AS IndexId,
CreateDate,
ModifyDate,
object_id AS ObjectId
FROM TableScript
UNION ALL
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
0 AS IndexId,
CreateDate,
ModifyDate,
object_id AS ObjectId
FROM Defaults
UNION ALL
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
IndexId,
CreateDate,
ModifyDate,
object_id AS ObjectId
FROM Indexes
UNION ALL
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
0 AS IndexId,
CreateDate,
ModifyDate,
object_id AS ObjectId
FROM Checks
UNION ALL
SELECT TYPE,
FQN,
SchemaName,
TableName,
ObjectName,
DropScript,
CreateScript,
0 AS IndexId,
CreateDate,
ModifyDate,
object_id AS ObjectId
FROM ForeignKeys'),
( 'PermissionsRecursive', 'WITH Perm
AS (SELECT d.Class,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
s.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectName,
Permission,
StateDesc AS StateDesc,
p.principal_id
FROM sys.database_permissions d (NOLOCK)
INNER JOIN sys.database_principals p (NOLOCK) ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.schemas s (NOLOCK) ON s.schema_id = d.major_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission) ca
WHERE d.class = 3 /*''SCHEMA''*/
UNION ALL
SELECT d.Class,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
DB_NAME() AS ObjectName,
Permission,
StateDesc AS StateDesc,
p.principal_id
FROM sys.database_permissions d (NOLOCK)
INNER JOIN sys.database_principals p (NOLOCK) ON d.grantee_principal_id = p.principal_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission) ca
WHERE d.class = 0 /*DATABASE*/
UNION ALL
SELECT d.Class,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
''['' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''].['' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']'' + ISNULL('' (['' + co.NAME COLLATE SQL_Latin1_General_CP1_CI_AS
+ '']) '', '''') AS ObjectName,
Permission,
StateDesc AS StateDesc,
p.principal_id
FROM sys.database_permissions d (NOLOCK)
INNER JOIN sys.database_principals p (NOLOCK) ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.objects o (NOLOCK) ON o.object_id = d.major_id
INNER JOIN sys.schemas s (NOLOCK) ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.columns co ON co.object_id = o.object_id
AND co.column_id = d.minor_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission) ca
WHERE d.class = 1 /*OBJECT OR COLUMN*/
AND p.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''U'', ''G'', ''S'', ''R'')
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')),
RoleMembers
AS (SELECT rp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS RoleName,
mp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
a.role_principal_id AS RoleId,
a.member_principal_id AS MemberId
FROM sys.database_role_members a (NOLOCK)
INNER JOIN sys.database_principals rp (NOLOCK) ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals mp (NOLOCK) ON mp.principal_id = a.member_principal_id
WHERE mp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')
AND mp.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''U'', ''G'', ''S'', ''R'')),
users
AS (SELECT Name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
principal_id AS MemberId,
type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS UserType,
create_date AS CreateDate,
is_fixed_role AS IsFixedRole
FROM sys.database_principals (NOLOCK)
WHERE TYPE IN (''S'', ''U'', ''G'', ''R'')
AND NAME NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')),
MemberLinks
AS (SELECT MemberName AS RoleName,
MemberId AS RoleId,
MemberName,
MemberId,
UserType,
CreateDate,
IsFixedRole,
0 AS [Level],
CAST('''' AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CI_AS AS HierarchyTree
FROM users
UNION ALL
SELECT r.RoleName,
r.RoleId,
l.MemberName,
l.MemberId,
l.UserType,
NULL AS CreateDate,
NULL AS IsFixedRole,
l.[Level] + 1 AS [Level],
CAST(l.HierarchyTree COLLATE SQL_Latin1_General_CP1_CI_AS + CASE WHEN l.HierarchyTree COLLATE SQL_Latin1_General_CP1_CI_AS = '''' THEN ''''
ELSE '',''
END + r.RoleName COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CI_AS AS HierarchyTree
FROM MemberLinks l
INNER JOIN RoleMembers r ON r.MemberId = l.RoleId)
SELECT MemberName,
RoleName,
[Level],
UserType,
0 AS Class,
''ROLE_MEMBER'' AS ClassName,
RoleName AS ObjectName,
''ROLE_MEMBER'' AS Permission,
''GRANT'' AS StateDesc,
HierarchyTree,
RoleId,
MemberId
FROM MemberLinks
WHERE RoleId <> MemberId
UNION ALL
SELECT a.MemberName,
a.RoleName,
[Level],
a.UserType,
p.Class,
p.ClassName,
p.ObjectName,
p.Permission,
p.StateDesc,
HierarchyTree,
a.RoleId,
a.MemberId
FROM MemberLinks a
INNER JOIN Perm p ON p.principal_id = a.RoleId'), ( 'TableUsage', 'SELECT QUOTENAME(s.NAME) + ''.'' + QUOTENAME(T.name) AS FQN,
s.name AS SchemaName,
t.name AS TableName,
CASE WHEN p.TableType = 1 THEN ''CLUSTERED''
ELSE ''HEAP''
END AS TableDesc,
p.Rows,
CAST(CASE WHEN p.ROWS > 0 THEN p.UsedPages * 8 * 1000.0 / p.ROWS
END AS NUMERIC(12, 2)) AS AvgBytesPerRow,
p.UsedPages * 8 AS UsedKB,
p.ReservedPages * 8 AS ReservedKB,
p.UsedIndexPages * 8 AS UsedIndexKB,
p.ReservedIndexPages * 8 AS ReservedIndexKB,
(p.UsedPages + ISNULL(p.UsedIndexPages, 0)) * 8 AS TotalUsedKB,
(p.ReservedPages + ISNULL(p.ReservedIndexPages, 0)) * 8 AS TotalReservedKB,
CAST(CASE WHEN p.ReservedPages > 0 THEN p.UsedPages * 100.00 / p.ReservedPages
END AS NUMERIC(6, 2)) AS [Used%],
CAST(CASE WHEN p.ReservedIndexPages > 0 THEN p.UsedIndexPages * 100.00 / p.ReservedIndexPages
END AS NUMERIC(6, 2)) AS [UsedIndex%],
CAST(CASE WHEN (p.ReservedPages + ISNULL(p.ReservedIndexPages, 0)) > 0
THEN (p.UsedPages + ISNULL(p.UsedIndexPages, 0)) * 100.00 / (p.ReservedPages + ISNULL(p.ReservedIndexPages, 0))
END AS NUMERIC(6, 2)) AS [TotalUsed%],
p.NonClustIndCnt,
p.PartitionCount,
t.create_date AS CreateDate,
t.modify_date AS ModifyDate,
t.object_id AS ObjectId
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.schema_id = t.schema_id
CROSS APPLY (SELECT COUNT(DISTINCT (CASE WHEN ddps.index_id > 1 THEN ddps.index_id
END)) AS NonClustIndCnt,
MAX(CASE WHEN ddps.index_id < 2 THEN ddps.index_id
END) AS TableType,
SUM(CASE WHEN ddps.index_id < 2 THEN ddps.row_count
END) AS Rows,
SUM(CASE WHEN ddps.index_id < 2 THEN ddps.used_page_count
END) AS UsedPages,
SUM(CASE WHEN ddps.index_id < 2 THEN ddps.Reserved_page_count
END) AS ReservedPages,
SUM(CASE WHEN ddps.index_id < 2 THEN 1
ELSE 0
END) AS PartitionCount,
SUM(CASE WHEN ddps.index_id >= 2 THEN ddps.used_page_count
END) AS UsedIndexPages,
SUM(CASE WHEN ddps.index_id >= 2 THEN ddps.Reserved_page_count
END) AS ReservedIndexPages
FROM sys.dm_db_partition_stats ddps (NOLOCK)
WHERE ddps.OBJECT_ID = t.OBJECT_ID) p
WHERE t.is_ms_shipped = 0'),
( 'PartitionSchemes', 'WITH s1
AS (SELECT ps.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS PartitionScheme,
pf.name COLLATE SQL_Latin1_General_CP1_CI_AS AS PartitionFunction,
pf.fanOut AS ValCnt,
ps.data_space_id,
ps.is_default AS IsDefault,
ps.function_id AS FunctionId,
''DROP PARTITION SCHEME ['' + ps.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']'' AS DropScript,
''CREATE PARTITION SCHEME ['' + ps.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''] AS PARTITION ['' + pf.NAME COLLATE SQL_Latin1_General_CP1_CI_AS
+ '']'' + CASE WHEN COUNT(DISTINCT dd.data_space_id) = 1 THEN '' ALL TO (['' + MAX(ds.NAME COLLATE SQL_Latin1_General_CP1_CI_AS) + ''])''
ELSE '' TO ('' + Util.dbo.StringConcat(QUOTENAME(DS.NAME COLLATE SQL_Latin1_General_CP1_CI_AS), '', '') + '')''
END AS CreateScript
FROM sys.partition_schemes ps (NOLOCK)
INNER JOIN sys.partition_functions pf (NOLOCK) ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dd (NOLOCK) ON dd.partition_scheme_id = ps.data_space_id
INNER JOIN sys.data_spaces ds (NOLOCK) ON ds.data_space_id = dd.data_space_id
GROUP BY ps.name COLLATE SQL_Latin1_General_CP1_CI_AS,
pf.name COLLATE SQL_Latin1_General_CP1_CI_AS,
pf.fanOut,
ps.name COLLATE SQL_Latin1_General_CP1_CI_AS,
ps.data_space_id,
ps.is_default,
ps.function_id)
SELECT PartitionScheme,
PartitionFunction,
ValCnt,
data_space_id,
IsDefault,
FunctionId,
DropScript,
CreateScript,
(SELECT 1 AS tag, NULL AS parent, CHAR (13) + CreateScript + CHAR (13) AS [Q!1!Q!CDATA]
FOR
XML EXPLICIT,
TYPE) AS CreateScriptXML
FROM s1'),
( 'PartitionFunctions', 'WITH part
AS (SELECT pf.name COLLATE SQL_Latin1_General_CP1_CI_AS AS PartitionFunction,
pf.fanOut AS ValCnt,
pf.boundary_value_on_right AS IsRightBoundary,
ct.ColumnType AS ParameterType,
pf.create_date AS CreateDate,
pf.modify_date AS ModifyDate,
pf.function_id AS FunctionId,
''DROP PARTITION FUNCTION ['' + pf.NAME COLLATE SQL_Latin1_General_CP1_CI_AS + '']'' AS DropScript,
''CREATE PARTITION FUNCTION ['' + pf.name COLLATE SQL_Latin1_General_CP1_CI_AS + '']('' + ct.ColumnType + '') AS RANGE ''
+ CASE WHEN boundary_value_on_right = 1 THEN ''RIGHT''
ELSE ''LEFT''
END + '' FOR VALUES('' + Util.dbo.StringConcat(CASE WHEN y.name LIKE ''%char''
OR y.name = ''sysname'' THEN '''''''' + CAST(rv.VALUE AS VARCHAR(4000)) + ''''''''
WHEN y.name LIKE ''%date%''
OR y.name LIKE ''%time%'' THEN '''''''' + CAST(rv.VALUE AS VARCHAR) + ''''''''
WHEN y.collation_name IS NULL
AND y.precision > 0
AND y.name NOT LIKE ''%date%''
AND y.name NOT LIKE ''%time%'' THEN CAST(rv.VALUE AS VARCHAR)
END, '', '') + '')'' AS CreateScript
FROM sys.partition_functions pf (NOLOCK)
INNER JOIN sys.partition_parameters pp (NOLOCK) ON pf.function_id = pp.function_id
AND pp.parameter_id = 1
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = pp.user_type_id
INNER JOIN sys.databases db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, pp.max_length, pp.precision, pp.scale, pp.collation_name, db.collation_name) ct
INNER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = pf.function_id
AND RV.parameter_id = 1
GROUP BY pf.name COLLATE SQL_Latin1_General_CP1_CI_AS,
pf.fanOut,
pf.boundary_value_on_right,
ct.ColumnType,
pf.function_id,
pf.create_date,
pf.modify_date)
SELECT PartitionFunction,
ValCnt,
IsRightBoundary,
ParameterType,
CreateDate,
ModifyDate,
FunctionId,
DropScript,
CreateScript
FROM part'), ( 'IndexPhysicalStats', 'SELECT o.type COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectType,
s.name AS SchemaName,
o.name AS ObjectName,
i.name AS IndexName,
i.index_id AS IndexId,
i.is_primary_key AS IsPrimaryKey,
i.is_unique AS IsUnique,
i.is_unique_constraint AS IsUniqueConstraint,
p.PartitionCnt,
p.partition_number AS PartitionNumber,
p.ROWS AS PartitionRows,
ps.index_depth AS Depth,
ps.index_level AS Level,
ps.record_count AS LevelRows,
ps.page_count AS Pages,
CAST(ps.avg_page_space_used_in_percent AS NUMERIC(6, 2)) AS PagePercentFull,
ps.min_record_size_in_bytes AS MinLen,
ps.max_record_size_in_bytes AS MaxLen,
ps.avg_record_size_in_bytes AS AvgLen,
i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.name COLLATE SQL_Latin1_General_CP1_CI_AS AS DataSpace,
ds.type COLLATE SQL_Latin1_General_CP1_CI_AS AS DataSpaceType,
p.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DataCompression,
o.object_id
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
INNER JOIN sys.data_spaces ds (NOLOCK) ON i.data_space_id = ds.data_space_id
CROSS APPLY (SELECT TOP 1
*,
COUNT(p.index_id) OVER (PARTITION BY (SELECT 0)) AS PartitionCnt
FROM sys.partitions p (NOLOCK)
WHERE p.OBJECT_ID = o.OBJECT_ID
AND p.index_id = i.index_id
AND p.rows > 0
ORDER BY p.rows DESC) p
CROSS APPLY Metadata.GetIndexPhysicalStats(DB_ID(), o.object_id, i.index_id, p.partition_number, ''DETAILED'') ps
WHERE i.index_id > 0
AND o.type IN (''U'',''V'')
AND i.is_disabled = 0
AND ds.type IN (''FG'', ''PS'')
AND o.is_ms_shipped = 0'), ( 'ObjectScripts', 'SELECT [Type] COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
FQN,
SchemaName,
ObjectName,
NULL AS SecondaryName,
NULL AS IndexId,
CreateDefinition,
Definition AS CreateScript,
DropScript,
AlterScript,
IndexScript,
AnsiSQL,
AssemblyName,
CreateDate,
ModifyDate,
ObjectId
FROM Metadata.ProcedureScripts
UNION ALL
SELECT [Type] COLLATE SQL_Latin1_General_CP1_CI_AS AS [Type],
FQN,
SchemaName,
TableName AS ObjectName,
CASE WHEN [Type] COLLATE SQL_Latin1_General_CP1_CI_AS <> ''U'' THEN ObjectName
END AS SecondaryName,
CASE WHEN IndexId > 0 THEN IndexId
END AS IndexId,
CASE [Type] COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN ''AF'' THEN ''AGGREGATE''
WHEN ''FS'' THEN ''FUNCTION''
WHEN ''FT'' THEN ''FUNCTION''
WHEN ''PG'' THEN ''PLAN GUIDE''
WHEN ''PC'' THEN ''PROCEDURE''
WHEN ''RF'' THEN ''PROCEDURE''
WHEN ''X'' THEN ''PROCEDURE''
WHEN ''SQ'' THEN ''QUEUE''
WHEN ''R'' THEN ''RULE''
WHEN ''IT'' THEN ''TABLE''
WHEN ''S'' THEN ''TABLE''
WHEN ''TA'' THEN ''TRIGGER''
WHEN ''U'' THEN ''TABLE''
WHEN ''PK'' THEN ''PRIMARY KEY''
WHEN ''D'' THEN ''DEFAULT''
WHEN ''UQ'' THEN ''UNIQUE CONSTRAINT''
WHEN ''C'' THEN ''CHECK''
WHEN ''F'' THEN ''FOREIGN KEY''
WHEN ''TR'' THEN ''TRIGGER''
WHEN ''TT'' THEN ''TYPE''
WHEN ''SN'' THEN ''SYNONYM''
WHEN ''V'' THEN ''VIEW''
WHEN ''FN'' THEN ''FUNCTION''
WHEN ''IF'' THEN ''FUNCTION''
WHEN ''TF'' THEN ''FUNCTION''
WHEN ''P'' THEN ''PROCEDURE''
WHEN ''I'' THEN ''INDEX''
END COLLATE SQL_Latin1_General_CP1_CI_AS AS CreateDefinition,
CreateScript COLLATE SQL_Latin1_General_CP1_CI_AS AS CreateScript,
DropScript COLLATE SQL_Latin1_General_CP1_CI_AS AS DropScript,
NULL AS AlterScript,
NULL AS IndexScript,
NULL AS AnsiSQL,
NULL AS AssemblyName,
CreateDate,
ModifyDate,
ObjectId
FROM Metadata.TableScripts'),
( 'ServerPrincipals', 'SELECT rp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS RoleName,
mp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
mp.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberType,
mp.Type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberTypeName,
''EXEC master..sp_addsrvrolemember @loginame = N'''''' + mp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''''', @rolename = N'''''' + rp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''''''' AS AddSQL,
''EXEC master..sp_dropsrvrolemember @loginame = N'''''' + mp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''''', @rolename = N'''''' + rp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''''''' AS DropSQL,
mp.principal_id AS MemberPrincipalId,
rp.principal_id AS RolePrincipalId
FROM sys.server_role_members a (NOLOCK)
INNER JOIN sys.server_principals rp (NOLOCK) ON rp.principal_id = a.role_principal_id
INNER JOIN sys.server_principals mp (NOLOCK) ON mp.principal_id = a.member_principal_id
WHERE mp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''cdc'')
AND mp.type COLLATE SQL_Latin1_General_CP1_CI_AS IN (''U'', ''G'', ''S'', ''R'')'), ( 'DatabaseFiles', 'SELECT
fg.name COLLATE SQL_Latin1_General_CP1_CI_AS AS filegroup,
mf.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeDesc,
mf.name COLLATE SQL_Latin1_General_CP1_CI_AS AS FileName,
CAST(i.size_on_disk_bytes * 1.0 / 1024 / 1024 / 1024 AS NUMERIC(12, 4))AS SizeOnDiskGB,
CAST(mf.size * 8.0 / 1024 / 1024 AS NUMERIC(12, 4))AS SizeGB,
CAST(FILEPROPERTY(mf.name, ''SpaceUsed'') / 128.000 / 1024 AS NUMERIC(12, 4))AS UsedGB,
CAST((mf.size - FILEPROPERTY(mf.name, ''SpaceUsed'')) / 128.000 / 1024 AS NUMERIC(12, 4))AS FreeGB,
CAST((mf.size - FILEPROPERTY(mf.name, ''SpaceUsed'')) * 100.00 / mf.size AS NUMERIC(12, 4))AS [Free%],
CAST(NULLIF(mf.max_size, -1) * 8.0 / 1024 / 1024 AS NUMERIC(12, 4))AS MaxSizeGB,
CAST(CASE
WHEN mf.is_percent_growth = 0
THEN mf.growth
END / 128.000 AS NUMERIC(12, 4))AS GrowthMB,
CASE
WHEN mf.is_percent_growth = 1
THEN mf.growth
END AS [Growth%],
i.sample_ms AS SampleMs,
i.num_of_reads AS NumOfReads,
CAST(i.num_of_bytes_read * 1.0 / 1024 / 1024 AS NUMERIC(12, 4))AS ReadMB,
i.io_stall_read_ms AS IOStallReadMs,
i.num_of_writes AS NumOfWrites,
CAST(i.num_of_bytes_written * 1.0 / 1024 AS NUMERIC(20, 2))AS WrittenMB,
i.io_stall_write_ms AS IOStallWriteMS,
i.io_stall AS IOStall,
mf.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
mf.is_media_read_only AS IsMediaReadOnly,
mf.is_read_only AS IsReadOnly,
mf.is_sparse AS IsSparse,
mf.is_percent_growth AS IsPercentGrowth,
mf.data_space_id AS DataSpaceId,
mf.file_id AS FileId,
mf.type AS Type,
mf.physical_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PhysicalName,
SUBSTRING(mf.physical_name, 1, LEN(mf.physical_name) - CHARINDEX(''\'', REVERSE(mf.physical_name)))COLLATE SQL_Latin1_General_CP1_CI_AS AS [Path]
FROM sys.database_files AS mf(NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS ds(NOLOCK)ON mf.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.filegroups AS fg(NOLOCK)ON ds.data_space_id = fg.data_space_id
OUTER APPLY Metadata.GetIOVirtualFileStats(DB_ID(), mf.file_id)AS i'), ( 'PartitionRowInfo', 'SELECT s.name AS SchemaName,
t.[Name] AS ObjectName,
i.index_id AS IndexId,
CASE ds.type
WHEN ''FG'' THEN pd.[Rows]
WHEN ''PS'' THEN p2.ROWS
END AS [Rows],
rv.PartitionCnt,
p2.partition_number AS PartitionId,
p2.PartPreVal,
LeftCriteria,
rv.PartCol,
RightCriteria,
ISNULL(p2.PartVal, PartMaxVal) AS PartVal,
DS.type COLLATE SQL_Latin1_General_CP1_CI_AS AS DSType,
ds.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DSTypeDesc,
rv.BoundaryOnRight,
t.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectType,
t.create_date AS CreateDate,
t.[object_id]
FROM sys.indexes i (NOLOCK)
INNER JOIN SYS.data_spaces ds (NOLOCK) ON ds.data_space_id = i.data_space_id
INNER JOIN sys.objects t (NOLOCK) ON t.object_id = i.object_id
INNER JOIN sys.schemas s (NOLOCK) ON t.schema_id = s.schema_id
LEFT OUTER JOIN sys.partition_schemes ps (NOLOCK) ON ps.data_space_id = ds.data_space_id
AND ds.TYPE = ''PS''
LEFT OUTER JOIN sys.partitions pd (NOLOCK) ON pd.index_id = i.index_id
AND pd.object_id = i.object_id
AND ds.type = ''FG''
OUTER APPLY (SELECT TOP 1
pf.boundary_value_on_right,
pf.fanout AS PartitionCnt,
rv.[Value] AS PartMaxVal,
pf.boundary_value_on_right AS BoundaryOnRight,
c.name AS PartCol
FROM sys.partition_functions pf (NOLOCK)
LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.index_id = i.index_id
AND ic.object_id = i.object_id
AND ic.partition_ordinal = 1
LEFT OUTER JOIN sys.columns c (NOLOCK) ON c.object_id = i.object_id
AND c.column_id = ic.column_id
INNER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = pf.function_id
WHERE ds.TYPE = ''PS''
AND pf.function_id = ps.function_id
ORDER BY rv.boundary_id DESC) rv
OUTER APPLY (SELECT p.Rows,
rv.Value AS PartVal,
rvp.VALUE AS PartPreVal,
p.partition_number
FROM sys.partitions p (NOLOCK)
LEFT OUTER JOIN sys.partition_range_values rv (NOLOCK) ON rv.function_id = ps.function_id
AND rv.boundary_id = p.partition_number
AND rv.parameter_id = 1
LEFT OUTER JOIN sys.partition_range_values rvp (NOLOCK) ON rvp.function_id = ps.function_id
AND rvp.boundary_id = rv.boundary_id - 1
AND rv.boundary_id > 1
AND rvp.parameter_id = 1
WHERE p.index_id = i.index_id
AND p.OBJECT_ID = i.OBJECT_ID
AND ds.TYPE = ''PS'') p2
OUTER APPLY (SELECT CASE WHEN rv.BoundaryOnRight = 0 THEN CASE WHEN p2.partition_number = rv.PartitionCnt THEN NULL
WHEN p2.partition_number > 1 THEN ''>''
ELSE NULL
END
WHEN rv.BoundaryOnRight = 1 THEN CASE WHEN p2.partition_number = rv.PartitionCnt THEN NULL
WHEN p2.partition_number > 1 THEN ''>=''
ELSE NULL
END
END AS LeftCriteria,
CASE WHEN rv.BoundaryOnRight = 0 THEN CASE WHEN p2.partition_number = rv.PartitionCnt THEN ''>''
WHEN p2.partition_number > 1 THEN ''<=''
ELSE ''<=''
END
WHEN rv.BoundaryOnRight = 1 THEN CASE WHEN p2.partition_number = rv.PartitionCnt THEN ''>=''
WHEN p2.partition_number > 1 THEN ''<''
ELSE ''<''
END
END AS RightCriteria
WHERE ds.TYPE = ''PS'') rf
WHERE i.index_id <= 1') ) d (Name, SQL))
--#endregion
SELECT @SQL = (SELECT CASE WHEN @CreateMetadataUser = 1
THEN 'USE [master]
' + CASE WHEN @DropAndRecreateMetadata = 1 THEN 'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N''Metadata'')
DROP LOGIN [Metadata]
' ELSE ''
END
+ 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N''Metadata'')
CREATE LOGIN [Metadata] WITH PASSWORD=N''dorot@6132'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, SID = 0x986D7AF90E30AC43959C2C8C1ACA7DB5
USE [Util]
' + CASE WHEN @DropAndRecreateMetadata = 1 THEN 'IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Metadata'')
DROP USER [Metadata]
' ELSE ''
END + 'IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Metadata'')
CREATE USER [Metadata] FOR LOGIN [Metadata] WITH DEFAULT_SCHEMA=[Metadata]
GRANT EXECUTE TO [Metadata]
GRANT SELECT TO [Metadata]
GRANT VIEW DEFINITION TO [Metadata]
' ELSE ''
END + 'DECLARE @SQL VARCHAR(MAX)
' + Util.dbo.StringConcat(SQL, '
')
FROM (SELECT 'USE [' + d.DatabaseName + ']
IF NOT EXISTS(SELECT * FROM sys.schemas (NOLOCK) WHERE NAME = ''Metadata'')
EXEC(''CREATE SCHEMA Metadata AUTHORIZATION [dbo]'')
'
+ CASE WHEN @CreateMetadataUser = 1
THEN CASE WHEN @DropAndRecreateMetadata = 1
THEN 'IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Metadata'')
DROP USER [Metadata]
' ELSE ''
END + '
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''Metadata'')
CREATE USER [Metadata] FOR LOGIN [Metadata] WITH DEFAULT_SCHEMA=[Metadata]
GRANT REFERENCES TO [Metadata]
GRANT SHOWPLAN TO [Metadata]
GRANT VIEW DATABASE STATE TO [Metadata]
GRANT VIEW DEFINITION TO [Metadata]
GRANT CONNECT TO [Metadata]
EXEC sp_addrolemember N''db_datareader'', N''Metadata''
' ELSE ''
END + Util.dbo.StringConcat('BEGIN TRY
' + CASE WHEN Type = 'FUNCTION' THEN ' IF OBJECT_ID(''Metadata.' + s.name + ''') IS NOT NULL DROP FUNCTION Metadata.' + s.name
WHEN Type = 'VIEW' THEN ' IF OBJECT_ID(''Metadata.' + s.name + ''') IS NULL EXEC(''CREATE VIEW Metadata.' + s.name + ' AS SELECT 1 AS ID'')'
END + '
END TRY
BEGIN CATCH
SELECT ErrorNumber = ERROR_NUMBER(),
ErrorSeverity = ERROR_SEVERITY(),
ErrorState = ERROR_STATE(),
ErrorProcedure = ERROR_PROCEDURE(),
ErrorLine = ERROR_LINE(),
ErrorMessage = ERROR_MESSAGE(),
Command = ''CREATE VIEW Metadata.' + s.name + ' AS SELECT 1 AS ID''
END CATCH
BEGIN TRY
SET @SQL = ''' + CASE WHEN Type = 'FUNCTION' THEN ''
WHEN Type = 'VIEW' THEN 'ALTER VIEW Metadata.' + s.name + '
AS
'
END + REPLACE(s.SQL, '''', '''''')
+ '''
EXEC(@SQL)
END TRY
BEGIN CATCH
SELECT ErrorNumber = ERROR_NUMBER(),
ErrorSeverity = ERROR_SEVERITY(),
ErrorState = ERROR_STATE(),
[Database] = DB_NAME(),
ErrorProcedure = ERROR_PROCEDURE(),
ErrorLine = ERROR_LINE(),
ErrorMessage = ERROR_MESSAGE(),
Command = (SELECT 1 AS tag, NULL AS parent, CHAR (13) COLLATE SQL_Latin1_General_CP1_CI_AS + @SQL + CHAR (13) COLLATE SQL_Latin1_General_CP1_CI_AS AS [Q!1!Q!CDATA]
FOR XML EXPLICIT,
TYPE)
END CATCH
', '
') AS SQL
FROM (SELECT 'FUNCTION' AS Type,
Name,
SQL
FROM Functions
UNION ALL
SELECT 'VIEW' AS Type,
Name,
SQL
FROM VIEWS) s
CROSS JOIN dbs d
GROUP BY d.DatabaseName) k) + ISNULL((SELECT 'USE DBA
IF OBJECT_ID(''Metadata.PermissionsAll'') IS NOT NULL DROP VIEW Metadata.PermissionsAll
EXEC(''CREATE VIEW Metadata.PermissionsAll
AS
' + Util.dbo.StringConcat('SELECT ''''' + d.DatabaseName + ''''' COLLATE SQL_Latin1_General_CP1_CI_AS AS DbName,
*
FROM ' + d.DatabaseName + '.Metadata.Permissions', '
UNION ALL
') + ''')
IF OBJECT_ID(''Metadata.PrincipalsAll'') IS NOT NULL DROP VIEW Metadata.PrincipalsAll
EXEC(''CREATE VIEW Metadata.PrincipalsAll
AS
' + Util.dbo.StringConcat('SELECT ''''' + d.DatabaseName + ''''' COLLATE SQL_Latin1_General_CP1_CI_AS AS DbName,
*
FROM ' + d.DatabaseName + '.Metadata.Principals', '
UNION ALL
') + ''')
IF OBJECT_ID(''Metadata.PermissionsRecursiveAll'') IS NOT NULL DROP VIEW Metadata.PermissionsRecursiveAll
EXEC(''CREATE VIEW Metadata.PermissionsRecursiveAll
AS
' + Util.dbo.StringConcat('SELECT ''''' + d.DatabaseName + ''''' COLLATE SQL_Latin1_General_CP1_CI_AS AS DbName,
*
FROM ' + d.DatabaseName + '.Metadata.PermissionsRecursive', '
UNION ALL
') + ''')
IF OBJECT_ID(''Metadata.RoleMembersAll'') IS NOT NULL DROP VIEW Metadata.RoleMembersAll
EXEC(''CREATE VIEW Metadata.RoleMembersAll
AS
' + Util.dbo.StringConcat('SELECT ''''' + d.DatabaseName + ''''' COLLATE SQL_Latin1_General_CP1_CI_AS AS DbName,
*
FROM ' + d.DatabaseName + '.Metadata.RoleMembers', '
UNION ALL
') + ''')
IF OBJECT_ID(''Metadata.RoleMembersRecursiveAll'') IS NOT NULL DROP VIEW Metadata.RoleMembersRecursiveAll
EXEC(''CREATE VIEW Metadata.RoleMembersRecursiveAll
AS
' + Util.dbo.StringConcat('SELECT ''''' + d.DatabaseName + ''''' COLLATE SQL_Latin1_General_CP1_CI_AS AS DbName,
*
FROM ' + d.DatabaseName + '.Metadata.RoleMembersRecursive', '
UNION ALL
') + ''')
IF OBJECT_ID(''Metadata.DatabaseFilesAll'') IS NOT NULL DROP VIEW Metadata.DatabaseFilesAll
EXEC(''CREATE VIEW Metadata.DatabaseFilesAll
AS
' + Util.dbo.StringConcat('SELECT ''''' + d.DatabaseName + ''''' COLLATE SQL_Latin1_General_CP1_CI_AS AS DbName,
*
FROM ' + d.DatabaseName + '.Metadata.DatabaseFiles', '
UNION ALL
') + ''')
'
FROM dbs d
WHERE @CreateDBAAllViews = 1
AND DB_ID('DBA') IS NOT NULL), '')
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecSQL = 1
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject sp_MetadataViewsCreate
GO