Skip to content

Instantly share code, notes, and snippets.

@rtweeks

rtweeks/mssql-eyewkas.sql

Last active February 26, 2018 07:29
Show Gist options
  • Save rtweeks/62d8fb9c6ca3de1195d9 to your computer and use it in GitHub Desktop.
Save rtweeks/62d8fb9c6ca3de1195d9 to your computer and use it in GitHub Desktop.
Download ZIP
MSSQL Database structure extractor
Raw
mssql-eyewkas.sql
-- Everything You Ever Wanted to Know About the Structure (returned as XML)
SET NOCOUNT ON;
DECLARE @VersionString AS NVARCHAR(100);
DECLARE @ver AS tinyint;
SET @VersionString = CAST(SERVERPROPERTY('productversion') AS NVARCHAR(100));
SET @ver = CAST(LEFT(@VersionString, CHARINDEX(N'.', @VersionString) - 1) AS tinyint);
DECLARE @EventGroups AS TABLE (
[object_id] int NOT NULL,
type int NOT NULL,
event_group_type_desc nvarchar(60) NULL,
PRIMARY KEY (object_id, type)
);
CREATE TABLE #EventGroupsTemp (
[object_id] int NOT NULL,
type int NOT NULL,
event_group_type_desc nvarchar(60) NULL
);
DECLARE @TableTypeLinks AS TABLE (
[user_type_id] int NOT NULL,
[type_table_object_id] int NOT NULL
);
CREATE TABLE #TableTypeLinksTemp (
[user_type_id] int NOT NULL,
[type_table_object_id] int NOT NULL
);
DECLARE @SpatialIndexInfo AS TABLE (
object_id int NOT NULL,
index_id int NOT NULL,
spatial_index_type_desc NVARCHAR(6) NOT NULL,
tessellation_scheme SYSNAME NOT NULL,
bb_xmin float,
bb_xmax float,
bb_ymin float,
bb_ymax float,
l1_grid NVARCHAR(10) NOT NULL,
l2_grid NVARCHAR(10) NOT NULL,
l3_grid NVARCHAR(10) NOT NULL,
l4_grid NVARCHAR(10) NOT NULL,
cells_per_object int
);
CREATE TABLE #SpatialIndexInfoTemp (
object_id int NOT NULL,
index_id int NOT NULL,
spatial_index_type_desc NVARCHAR(6) NOT NULL,
tessellation_scheme SYSNAME NOT NULL,
bb_xmin float,
bb_xmax float,
bb_ymin float,
bb_ymax float,
l1_grid NVARCHAR(10) NOT NULL,
l2_grid NVARCHAR(10) NOT NULL,
l3_grid NVARCHAR(10) NOT NULL,
l4_grid NVARCHAR(10) NOT NULL,
cells_per_object int
);
IF @ver > 9
BEGIN
EXEC sp_executesql N'
INSERT INTO #EventGroupsTemp (object_id, type, event_group_type_desc)
SELECT
object_id,
type,
event_group_type_desc
FROM sys.events
WHERE event_group_type_desc IS NOT NULL;
';
EXEC sp_executesql N'
INSERT INTO #TableTypeLinksTemp (user_type_id, type_table_object_id)
SELECT
user_type_id,
type_table_object_id
FROM sys.table_types
WHERE type_table_object_id IS NOT NULL;
';
EXEC sp_executesql N'
INSERT INTO #SpatialIndexInfoTemp (
object_id,
index_id,
spatial_index_type_desc,
tessellation_scheme,
bb_xmin, bb_xmax,
bb_ymin, bb_ymax,
l1_grid, l2_grid, l3_grid, l4_grid,
cells_per_object
)
SELECT
si.object_id,
si.index_id,
LOWER(si.spatial_index_type_desc),
si.tessellation_scheme,
sit.bounding_box_xmin, sit.bounding_box_xmax, sit.bounding_box_ymin, sit.bounding_box_ymax,
sit.level_1_grid, sit.level_2_grid, sit.level_3_grid, sit.level_4_grid,
sit.cells_per_object
FROM sys.spatial_indexes si
JOIN sys.spatial_index_tessellations sit
ON si.object_id = sit.object_id
AND si.index_id = sit.index_id
';
END;
INSERT INTO @EventGroups ([object_id], type, event_group_type_desc)
SELECT [object_id], type, event_group_type_desc
FROM #EventGroupsTemp;
INSERT INTO @TableTypeLinks (user_type_id, type_table_object_id)
SELECT user_type_id, type_table_object_id
FROM #TableTypeLinksTemp;
INSERT INTO @SpatialIndexInfo (
object_id, index_id,
spatial_index_type_desc, tessellation_scheme,
bb_xmin, bb_xmax, bb_ymin, bb_ymax,
l1_grid, l2_grid, l3_grid, l4_grid,
cells_per_object
)
SELECT
object_id, index_id,
spatial_index_type_desc, tessellation_scheme,
bb_xmin, bb_xmax, bb_ymin, bb_ymax,
l1_grid, l2_grid, l3_grid, l4_grid,
cells_per_object
FROM #SpatialIndexInfoTemp;
DROP TABLE #EventGroupsTemp;
DROP TABLE #TableTypeLinksTemp;
DROP TABLE #SpatialIndexInfoTemp;
SET NOCOUNT OFF;
DECLARE @filegroups XML, @partition_functions XML, @partition_schemes XML,
@schemas XML, @roles XML, @assemblies XML, @synonyms XML,
@schema_collections XML, @sql_scalar_types XML, @clr_types XML,
@table_types XML, @fulltext_doctypes XML, @fulltext_catalogs XML,
@tables XML, @default_constraints XML, @primary_keys XML,
@unique_constraints XML, @check_constraints XML,
@service_broker_details XML;
SELECT @filegroups = (SELECT
fg.name AS "@name",
CASE
WHEN fg.is_default > 0 THEN 'default'
END AS "@default",
CASE
WHEN fg.is_read_only > 0 THEN 'read-only'
END AS "@read-only",
(SELECT
f.name AS "name",
CASE
WHEN f.state = 6 THEN 'offline'
END AS "offline",
-- Size
CAST(f.size AS BIGINT) * 8 AS "size",
'KB' AS "size-units",
-- Max size
CASE
WHEN f.max_size = -1 THEN 'available'
ELSE CAST(CAST(f.max_size AS BIGINT) * 8 AS NVARCHAR(15))
END AS "max_size",
CASE
WHEN f.max_size <> -1 THEN 'KB'
END AS "max-size-units",
-- File growth
CASE
WHEN f.is_percent_growth = 0 THEN f.growth * 8
ELSE f.growth
END AS "growth",
CASE
WHEN f.is_percent_growth = 0 THEN 'KB'
ELSE 'percent'
END AS "growth-units",
(SELECT
p.name AS "@name",
SQL_VARIANT_PROPERTY(p.value, 'BaseType') AS "@type",
p.value AS "text()"
FROM sys.extended_properties p
WHERE p.class = 22
AND p.major_id = f.file_id
FOR XML PATH('property'), TYPE)
FROM sys.database_files f
WHERE f.data_space_id = fg.data_space_id
AND f.type IN (0, 1)
AND f.state <> 7
FOR XML RAW('file'), TYPE),
(SELECT
p.name AS "@name",
SQL_VARIANT_PROPERTY(p.value, 'BaseType') AS "@type",
p.value AS "text()"
FROM sys.extended_properties p
WHERE p.class = 20
AND p.major_id = fg.data_space_id
FOR XML PATH('property'), TYPE)
FROM sys.filegroups fg
FOR XML PATH('filegroup'), TYPE);
WITH
TypeFields AS (
SELECT
t.user_type_id AS type_id,
s.name AS schema_name,
t.name AS name,
CASE
WHEN s.name = 'sys' AND t.name IN ('binary', 'char', 'varbinary', 'varchar') THEN 1
WHEN s.name = 'sys' AND t.name IN ('nchar', 'nvarchar') THEN 2
END AS max_length_divisor,
CASE
WHEN s.name = 'sys' AND t.name IN ('decimal', 'numeric') THEN 1
END AS scaled_integral
FROM sys.types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
)
SELECT @partition_functions = (SELECT
QUOTENAME(pf.name) AS "@name",
CASE
WHEN pf.boundary_value_on_right = 0 THEN 'left'
ELSE 'right'
END AS "@bounding-value-on",
(SELECT
QUOTENAME(t.schema_name) AS "@type-schema",
QUOTENAME(t.name) AS "@type",
pp.max_length / t.max_length_divisor AS "@capacity",
pp.precision * t.scaled_integral AS "@precision",
pp.scale * t.scaled_integral AS "@scale",
pp.collation_name AS "@collation",
(SELECT
v.value AS "value"
FROM sys.partition_range_values v
WHERE v.function_id = pp.function_id
AND v.parameter_id = pp.parameter_id
ORDER BY v.boundary_id
FOR XML RAW('boundary'), TYPE)
FROM sys.partition_parameters pp
JOIN TypeFields t ON pp.system_type_id = t.type_id
WHERE pp.function_id = pf.function_id
ORDER BY pp.parameter_id
FOR XML PATH('parameter'), TYPE),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 21
AND prop.major_id = pf.function_id
FOR XML PATH('property'), TYPE)
FROM sys.partition_functions pf
FOR XML PATH('partition-function'), TYPE);
SELECT @partition_schemes = (SELECT
QUOTENAME(ps.name) AS "@name",
QUOTENAME(pf.name) AS "@divided-by",
(SELECT
ds.name AS "name"
FROM sys.destination_data_spaces pd
JOIN sys.data_spaces ds ON pd.data_space_id = ds.data_space_id
WHERE pd.partition_scheme_id = ps.data_space_id
ORDER BY pd.destination_id
FOR XML RAW('filegroup'), TYPE),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 20
AND prop.major_id = ps.data_space_id
FOR XML PATH('property'), TYPE)
FROM sys.partition_schemes ps
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
FOR XML PATH('partition-scheme'), TYPE);
SELECT @schemas = (SELECT
QUOTENAME(s.name) AS "name",
QUOTENAME(p.name) AS "owner",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 3
AND prop.major_id = s.schema_id
FOR XML PATH('property'), TYPE)
FROM sys.schemas s
LEFT JOIN sys.database_principals p ON s.principal_id = p.principal_id
WHERE s.name NOT IN ('sys', 'dbo', 'INFORMATION_SCHEMA')
AND s.schema_id IN (
SELECT o.schema_id
FROM sys.objects o
WHERE o.type IN ('FN', 'IF', 'P', 'SN', 'SO', 'SQ', 'TF', 'U', 'V')
)
FOR XML RAW('schema'), TYPE);
SELECT @roles = (SELECT
QUOTENAME(r.name) AS "@name",
QUOTENAME(o.name) AS "@owner",
-- Membership
(SELECT
QUOTENAME(er.name) AS "@name"
FROM sys.database_role_members rm
JOIN sys.database_principals er ON rm.role_principal_id = er.principal_id
WHERE rm.member_principal_id = r.principal_id
FOR XML PATH('member-of'), TYPE),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 4
AND prop.major_id = r.principal_id
FOR XML PATH('property'), TYPE)
FROM sys.database_principals r
LEFT JOIN sys.database_principals o ON r.owning_principal_id = o.principal_id
WHERE r.type = 'R'
AND r.is_fixed_role = 0
AND r.principal_id <> 0
FOR XML PATH('role'), TYPE);
SELECT @assemblies = (SELECT
QUOTENAME(asm.name) AS "@name",
QUOTENAME(owner.name) AS "@owner",
REPLACE(LOWER(asm.permission_set_desc), '_', '-') AS "@permission-set",
(SELECT
p.name AS "@name",
SQL_VARIANT_PROPERTY(p.value, 'BaseType') AS "@type",
p.value AS "text()"
FROM sys.extended_properties p
WHERE p.class = 5
AND p.major_id = asm.assembly_id
FOR XML PATH('property'), TYPE),
asm.clr_name AS "text()"
FROM sys.assemblies asm
LEFT JOIN sys.database_principals owner ON asm.principal_id = owner.principal_id
WHERE asm.is_visible = 1
FOR XML PATH('clr-assembly'), TYPE);
SELECT @synonyms = (SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(syn.name) AS "@name",
syn.base_object_name AS "@for",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = syn.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.synonyms syn
JOIN sys.schemas s ON syn.schema_id = s.schema_id
FOR XML PATH('synonym'), TYPE);
SELECT @schema_collections = (SELECT
QUOTENAME(p.name) as "@owner",
xml_schema_namespace(s.name, xsc.name) AS "*",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 10
AND prop.major_id = xsc.xml_collection_id
FOR XML PATH('property'), TYPE)
FROM sys.xml_schema_collections xsc
JOIN sys.schemas s ON xsc.schema_id = s.schema_id
LEFT JOIN sys.database_principals p ON xsc.principal_id = p.principal_id
WHERE s.name <> 'sys' OR xsc.name <> 'sys'
FOR XML PATH('schema-collection'), TYPE);
WITH
TypeFields AS (
SELECT
t.user_type_id AS type_id,
s.name AS schema_name,
t.name AS name,
CASE
WHEN s.name = 'sys' AND t.name IN ('binary', 'char', 'varbinary', 'varchar') THEN 1
WHEN s.name = 'sys' AND t.name IN ('nchar', 'nvarchar') THEN 2
END AS max_length_divisor,
CASE
WHEN s.name = 'sys' AND t.name IN ('decimal', 'numeric') THEN 1
END AS scaled_integral
FROM sys.types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
)
SELECT @sql_scalar_types = (SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(t.name) AS "@name",
QUOTENAME(bt.name) AS "@base-type", -- Base type is always a system type, no schema qualification needed
CASE
WHEN t.max_length = -1 AND tf.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(t.max_length / tf.max_length_divisor AS VARCHAR(20))
END AS "@capacity",
t.precision * tf.scaled_integral AS "@precision",
t.scale * tf.scaled_integral AS "@scale",
t.collation_name AS "@collation",
CASE
WHEN t.is_nullable <> 0 THEN 'nullable'
END AS "@nullable",
-- Alias data type bound default (sp_bindefault)
(SELECT
QUOTENAME(ds.name) AS "@schema",
QUOTENAME(d.name) AS "@name",
sql.definition AS "text()"
FROM sys.objects d
JOIN sys.objects ds ON d.schema_id = ds.schema_id
JOIN sys.sql_modules sql ON d.object_id = sql.object_id
WHERE d.object_id = t.default_object_id
FOR XML PATH('default'), TYPE),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class IN (1, 6)
AND prop.major_id = t.user_type_id
FOR XML PATH('property'), TYPE)
FROM sys.types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.types bt ON t.system_type_id = bt.user_type_id
JOIN TypeFields tf ON bt.user_type_id = tf.type_id
WHERE t.is_user_defined <> 0
AND t.is_assembly_type = 0
AND t.user_type_id NOT IN (SELECT user_type_id FROM @TableTypeLinks)
FOR XML PATH('type'), TYPE);
SELECT @clr_types = (SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(t.name) AS "@name",
QUOTENAME(asm.name) AS "@assembly",
QUOTENAME(t.assembly_class) AS "@class",
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class IN (1, 6)
AND prop.major_id = t.user_type_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.assembly_types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.assemblies asm ON t.assembly_id = asm.assembly_id
FOR XML PATH('clr-type'), TYPE);
WITH
TypeFields AS (
SELECT
t.user_type_id AS type_id,
s.name AS schema_name,
t.name AS name,
CASE
WHEN s.name = 'sys' AND t.name IN ('binary', 'char', 'varbinary', 'varchar') THEN 1
WHEN s.name = 'sys' AND t.name IN ('nchar', 'nvarchar') THEN 2
END AS max_length_divisor,
CASE
WHEN s.name = 'sys' AND t.name IN ('decimal', 'numeric') THEN 1
END AS scaled_integral
FROM sys.types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
),
TableTypeKeyConstraints AS (
SELECT
kc.parent_object_id,
(SELECT
CASE kc.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END AS "@type",
CASE
WHEN i.type = 1 THEN 'clustered'
END AS "@clustered",
CASE
WHEN i.ignore_dup_key <> 0 THEN 'ignore-duplicates'
END AS "@ignore-duplicates",
(SELECT
QUOTENAME(c.name) AS "@name",
CASE
WHEN ic.is_descending_key > 0 THEN 'desc'
END AS "@desc"
FROM sys.index_columns ic
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.key_ordinal >= 1
ORDER BY ic.key_ordinal
FOR XML PATH('column'), TYPE) AS "*",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = kc.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE) AS "properties"
FOR XML PATH('key-constraint'), TYPE) as [constraint]
FROM sys.key_constraints kc
JOIN sys.schemas s
ON kc.schema_id = s.schema_id
JOIN @TableTypeLinks tt
ON kc.parent_object_id = tt.type_table_object_id
JOIN sys.indexes i
ON kc.parent_object_id = i.object_id
AND kc.unique_index_id = i.index_id
),
TableTypeCheckConstraints AS (
SELECT
tt.type_table_object_id AS parent_object_id,
cc.parent_column_id AS column_id, -- NULL for table-level CHECK constraints
(SELECT
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = cc.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE),
cc.definition AS "expression"
FOR XML PATH('check-constraint'), TYPE) AS [constraint]
FROM sys.check_constraints cc
JOIN @TableTypeLinks tt ON cc.parent_object_id = tt.type_table_object_id
)
SELECT @table_types = (SELECT
'1.1' AS "@eyewkas_ver",
QUOTENAME(s.name) AS "@schema",
QUOTENAME(t.name) AS "@name",
(SELECT QUOTENAME(c.name)
FROM sys.columns c
WHERE c.is_identity > 0
AND c.object_id = ttl.type_table_object_id) AS "@identity",
-- Columns
(SELECT
-- Attributes
QUOTENAME(c.name) AS "@name",
CASE
WHEN ct.schema_name <> 'sys' THEN QUOTENAME(ct.schema_name)
END AS "@type-schema",
QUOTENAME(ct.name) AS "@type",
CASE
WHEN c.max_length = -1 AND ct.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(c.max_length / ct.max_length_divisor AS VARCHAR(8))
END AS "@capacity",
c.precision * ct.scaled_integral AS "@precision",
c.scale * ct.scaled_integral AS "@scale",
c.collation_name AS "@collation",
CASE
WHEN c.is_nullable > 0 THEN 'nullable'
END AS "@nullable",
CASE
WHEN c.is_ansi_padded = 0 AND ct.schema_name = 'sys' AND ct.name IN ('char', 'varchar', 'binary', 'varbinary') -- but not nchar or nvarchar!
THEN 'not-ansi-padded'
END AS "@not-ansi-padded",
CASE
WHEN c.is_xml_document > 0 THEN 'full-xml-document'
END AS "@full-xml-document",
(SELECT
QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = c.xml_collection_id) AS "@xml_collection_id",
-- Child elements
(SELECT
CASE
WHEN cc.is_persisted <> 0 THEN 'persisted'
END AS "@persisted",
cc.definition AS "text()"
FROM sys.computed_columns cc
WHERE c.object_id = cc.object_id
AND c.name = cc.name
FOR XML PATH('computed-expression'), TYPE),
(SELECT
cc.[constraint]
FROM TableTypeCheckConstraints cc
WHERE cc.parent_object_id = ttl.type_table_object_id
AND cc.column_id = c.column_id
),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = ttl.type_table_object_id
AND prop.minor_id = c.column_id
FOR XML PATH('property'), TYPE)
FROM sys.columns c
LEFT JOIN TypeFields ct ON c.user_type_id = ct.type_id
WHERE c.object_id = ttl.type_table_object_id
ORDER BY c.column_id
FOR XML PATH('column'), TYPE),
-- Primary key and unique constraints
(SELECT
ttkc.[constraint]
FROM TableTypeKeyConstraints ttkc
WHERE ttkc.parent_object_id = ttl.type_table_object_id),
-- Table-level check constraints
(SELECT
ttcc.[constraint]
FROM TableTypeCheckConstraints ttcc
WHERE ttcc.parent_object_id = ttl.type_table_object_id
AND ttcc.column_id = 0),
-- Extended properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class IN (1, 6)
AND prop.major_id = ttl.type_table_object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.types t
JOIN @TableTypeLinks ttl ON t.user_type_id = ttl.user_type_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
FOR XML PATH('table-type'), TYPE);
SELECT @fulltext_doctypes = (SELECT
document_type AS "extension"
FROM sys.fulltext_document_types
FOR XML RAW('fulltext-document-type'), TYPE);
SELECT @fulltext_catalogs = (SELECT
QUOTENAME(fc.name) AS "name",
QUOTENAME(fc.[path]) AS "path",
CASE
WHEN fc.is_default > 0 THEN 'default'
END AS "default",
CASE
WHEN fc.is_accent_sensitivity_on > 0 THEN 'accent-sensitive'
END AS "accent-sensitive",
QUOTENAME(fg.name) AS "filegroup",
QUOTENAME(owner.name) AS "owner"
FROM sys.fulltext_catalogs fc
LEFT JOIN sys.database_principals owner ON fc.principal_id = owner.principal_id
LEFT JOIN sys.filegroups fg ON fc.data_space_id = fg.data_space_id
FOR XML RAW('fulltext-catalog'), TYPE);
WITH
DataSpaceHelper AS (
SELECT
o.object_id,
i.index_id,
i.type,
QUOTENAME(ds.name) AS "name",
(SELECT
QUOTENAME(c.name) AS "name"
FROM sys.index_columns ic
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.partition_ordinal > 0
AND ds.type = 'PS'
ORDER BY ic.partition_ordinal
FOR XML RAW('partition-column'), TYPE) AS partition_columns
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE (ds.type <> 'FG' OR ds.is_default <> 1)
),
TypeFields AS (
SELECT
t.user_type_id AS type_id,
s.name AS schema_name,
t.name AS name,
CASE
WHEN s.name = 'sys' AND t.name IN ('binary', 'char', 'varbinary', 'varchar') THEN 1
WHEN s.name = 'sys' AND t.name IN ('nchar', 'nvarchar') THEN 2
END AS max_length_divisor,
CASE
WHEN s.name = 'sys' AND t.name IN ('decimal', 'numeric') THEN 1
END AS scaled_integral
FROM sys.types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
)
SELECT @tables = (SELECT
QUOTENAME(s.name) AS "schema",
QUOTENAME(t.name) AS "name",
(SELECT QUOTENAME(c.name)
FROM sys.columns c
WHERE c.is_rowguidcol > 0
AND c.object_id = t.object_id) AS "rowguidcol",
(SELECT QUOTENAME(c.name)
FROM sys.columns c
WHERE c.is_identity > 0
AND c.object_id = t.object_id) AS "identity",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = t.principal_id) AS "owner",
QUOTENAME(lob_storage.name) AS "textimage-on",
heap_storage.name AS "rows-on",
-- Columns
(SELECT
-- Attributes
QUOTENAME(c.name) AS "@name",
CASE
WHEN ct.schema_name <> 'sys' THEN QUOTENAME(ct.schema_name)
END AS "@type-schema",
QUOTENAME(ct.name) AS "@type",
CASE
WHEN c.max_length = -1 AND ct.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(c.max_length / ct.max_length_divisor AS VARCHAR(8))
END AS "@capacity",
c.precision * ct.scaled_integral AS "@precision",
c.scale * ct.scaled_integral AS "@scale",
c.collation_name AS "@collation",
CASE
WHEN c.is_nullable > 0 THEN 'nullable'
END AS "@nullable",
CASE
WHEN c.is_ansi_padded = 0 AND ct.schema_name = 'sys' AND ct.name IN ('char', 'varchar', 'binary', 'varbinary') -- but not nchar or nvarchar!
THEN 'not-ansi-padded'
END AS "@not-ansi-padded",
CASE
WHEN c.is_filestream > 0 THEN 'filestream'
END AS "@filestream",
CASE
WHEN c.is_replicated > 0 THEN 'replicated'
END AS "@replicated",
CASE
WHEN c.is_non_sql_subscribed > 0 THEN 'non-sql-subscribed'
END AS "@non-sql-subscribed",
CASE
WHEN c.is_merge_published > 0 THEN 'merge-published'
END AS "@merge-published",
CASE
WHEN c.is_dts_replicated > 0 THEN 'dts-replicated'
END AS "@dts-replicated",
CASE
WHEN c.is_xml_document > 0 THEN 'full-xml-document'
END AS "@full-xml-document",
(SELECT
QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = c.xml_collection_id) AS "@xml_collection_id",
-- Child elements
(SELECT
CASE
WHEN cc.is_persisted > 0 THEN 'persisted'
END AS "@persisted",
cc.definition AS "text()"
FROM sys.computed_columns cc
WHERE c.object_id = cc.object_id
AND c.name = cc.name
FOR XML PATH('computed-expression'), TYPE),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = t.object_id
AND prop.minor_id = c.column_id
FOR XML PATH('property'), TYPE)
FROM sys.columns c
LEFT JOIN TypeFields ct ON c.user_type_id = ct.type_id
WHERE c.object_id = t.object_id
ORDER BY c.column_id
FOR XML PATH('column'), TYPE),
heap_storage.partition_columns,
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = t.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.filegroups lob_storage ON t.lob_data_space_id = lob_storage.data_space_id
LEFT JOIN DataSpaceHelper heap_storage ON t.object_id = heap_storage.object_id AND heap_storage.type = 0
FOR XML RAW('table'), TYPE);
SELECT @default_constraints = (SELECT
QUOTENAME(s.name) AS "@schema",
CASE
WHEN dc.is_system_named = 0 THEN QUOTENAME(dc.name)
END AS "@name",
QUOTENAME(t.name) AS "@table",
QUOTENAME(c.name) AS "@column",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = dc.principal_id) as "@owner",
dc.definition AS "text()"
FROM sys.default_constraints dc
JOIN sys.schemas s ON dc.schema_id = s.schema_id
JOIN sys.tables t ON dc.parent_object_id = t.object_id
JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
FOR XML PATH('default-constraint'), TYPE);
WITH
DataSpaceHelper AS (
SELECT
o.object_id,
i.index_id,
i.type,
QUOTENAME(ds.name) AS "name",
(SELECT
QUOTENAME(c.name) AS "name"
FROM sys.index_columns ic
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.partition_ordinal > 0
AND ds.type = 'PS'
ORDER BY ic.partition_ordinal
FOR XML RAW('partition-column'), TYPE) AS partition_columns
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE (ds.type <> 'FG' OR ds.is_default <> 1)
),
KeyConstraintsHelper AS (
SELECT
kc.type AS type,
QUOTENAME(s.name) AS "schema",
CASE
WHEN kc.is_system_named = 0 THEN QUOTENAME(kc.name)
END AS name,
QUOTENAME(t.name) AS "table",
CASE
WHEN i.type = 1 THEN 'clustered'
END AS "clustered",
CASE
WHEN i.fill_factor > 0 THEN i.fill_factor
END AS "fill-factor",
CASE
WHEN i.is_padded > 0 THEN 'padded'
END AS "padded",
CASE
WHEN i.allow_row_locks = 0 THEN 'no-row-locks'
END AS "no-row-locks",
CASE
WHEN i.allow_page_locks = 0 THEN 'no-page-locks'
END AS "no-page-locks",
ds.name AS "stored-on",
ds.partition_columns,
(SELECT
QUOTENAME(c.name) AS "@name",
CASE
WHEN ic.is_descending_key > 0 THEN 'desc'
END AS "@desc"
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.key_ordinal >= 1
ORDER BY ic.key_ordinal
FOR XML PATH('column'), TYPE) AS "key_columns",
(SELECT
QUOTENAME(c.name) AS "@name",
CASE
WHEN ic.is_included_column > 0 THEN 'included'
END AS "@included"
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.key_ordinal = 0
FOR XML PATH('column'), TYPE) AS "other_columns",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = kc.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE) AS "properties"
FROM sys.key_constraints kc
JOIN sys.schemas s ON kc.schema_id = s.schema_id
JOIN sys.tables t ON kc.parent_object_id = t.object_id
JOIN sys.indexes i
ON kc.parent_object_id = i.object_id
AND kc.unique_index_id = i.index_id
LEFT JOIN DataSpaceHelper ds
ON ds.object_id = i.object_id
AND ds.index_id = i.index_id
)
SELECT @primary_keys = (SELECT
kc."schema" AS "@schema",
kc.name AS "@name",
kc."table" AS "@table",
kc."clustered" AS "@clustered",
kc."stored-on" AS "@stored-on",
kc."fill-factor" AS "@fill-factor",
kc."padded" AS "@padded",
kc."no-row-locks" AS "@no-row-locks",
kc."no-page-locks" AS "@no-page-locks",
kc."key_columns" AS "*",
kc."other_columns" AS "*",
kc.partition_columns AS "*",
kc.properties AS "*"
FROM KeyConstraintsHelper kc
WHERE kc.type = 'PK'
FOR XML PATH('primary-key'), TYPE),
@unique_constraints = (SELECT
kc."schema" AS "@schema",
kc."name" AS "@name",
kc."table" AS "@table",
kc."stored-on" AS "@stored-on",
kc."fill-factor" AS "@fill-factor",
kc."padded" AS "@padded",
kc."no-row-locks" AS "@no-row-locks",
kc."no-page-locks" AS "@no-page-locks",
kc."key_columns" AS "*",
kc."other_columns" AS "*",
kc.partition_columns AS "*",
kc.properties AS "*"
FROM KeyConstraintsHelper kc
WHERE kc.type = 'UQ'
FOR XML PATH('unique-constraint'), TYPE);
WITH
DataSpaceHelper AS (
SELECT
o.object_id,
i.index_id,
i.type,
QUOTENAME(ds.name) AS "name",
(SELECT
QUOTENAME(c.name) AS "name"
FROM sys.index_columns ic
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.partition_ordinal > 0
AND ds.type = 'PS'
ORDER BY ic.partition_ordinal
FOR XML RAW('partition-column'), TYPE) AS partition_columns
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE (ds.type <> 'FG' OR ds.is_default <> 1)
)
SELECT @check_constraints = (SELECT
QUOTENAME(s.name) AS "@schema",
CASE
WHEN cc.is_system_named = 0 THEN QUOTENAME(cc.name)
END AS "@name",
QUOTENAME(t.name) AS "@table",
(SELECT QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = t.object_id
AND c.column_id = cc.parent_column_id) AS "@column",
CASE
WHEN cc.is_disabled > 0 THEN 'disabled'
END AS "@disabled",
CASE
WHEN cc.is_not_for_replication > 0 THEN 'not-for-replication'
END AS "@not-for-replication",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = cc.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE),
cc.definition AS "text()"
FROM sys.check_constraints cc
JOIN sys.tables t ON cc.parent_object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
FOR XML PATH('check-constraint'), TYPE);
SELECT @service_broker_details = (SELECT
(SELECT
QUOTENAME(msgtyp.name) AS "@name",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = msgtyp.principal_id) AS "@owner",
CASE
WHEN msgtyp.validation = 'E' THEN 'empty'
WHEN msgtyp.validation = 'X' THEN COALESCE(
(SELECT QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = msgtyp.xml_collection_id),
'well-formed-xml'
)
END AS "@validation",
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 15
AND prop.major_id = msgtyp.message_type_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.service_message_types msgtyp
WHERE msgtyp.name <> 'DEFAULT'
AND msgtyp.name NOT LIKE 'http://schemas.microsoft.com/%'
FOR XML PATH('service-message-type'), TYPE),
(SELECT
QUOTENAME(c.name) AS "@name",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = c.principal_id) AS "@owner",
(SELECT
QUOTENAME(msgtyp.name) AS "@type",
CASE
WHEN msg.is_sent_by_initiator <> 0 AND msg.is_sent_by_target <> 0 THEN 'any'
WHEN msg.is_sent_by_initiator <> 0 THEN 'initiator'
WHEN msg.is_sent_by_target <> 0 THEN 'target'
END AS "@sent-by"
FROM sys.service_contract_message_usages msg
JOIN sys.service_message_types msgtyp ON msg.message_type_id = msgtyp.message_type_id
WHERE msg.service_contract_id = c.service_contract_id
FOR XML PATH('message'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 16
AND prop.major_id = c.service_contract_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.service_contracts c
WHERE c.name <> 'DEFAULT'
AND c.name NOT LIKE 'http://schemas.microsoft.com/%'
FOR XML PATH('service-contract'), TYPE),
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(q.name) AS "@name",
CASE
WHEN q.is_receive_enabled <> 0 AND q.is_enqueue_enabled <> 0 THEN 'active'
END AS "@active",
CASE
WHEN q.is_retention_enabled <> 0 THEN 'retain-full-dialog'
END AS "@retain-full-dialog",
-- TODO: This may be referenced in sys.sql_dependencies, which would give a better breakdown of the name
q.activation_procedure AS "activation/@procedure",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = q.execute_as_principal_id) AS "activation/@execute-as",
CASE
WHEN q.activation_procedure IS NOT NULL THEN q.max_readers
END AS "activation/@max-instances",
CASE
WHEN q.is_activation_enabled = 0 AND q.activation_procedure IS NOT NULL THEN 'disabled'
END AS "activation/@disabled",
-- Properites
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = q.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.service_queues q
JOIN sys.schemas s ON q.schema_id = s.schema_id
WHERE q.is_ms_shipped = 0
FOR XML PATH('service-queue'), TYPE),
(SELECT
QUOTENAME(svc.name) AS "@name",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = svc.principal_id) AS "@owner",
QUOTENAME(qs.name) AS "queue/@schema",
QUOTENAME(q.name) AS "queue/@name",
(SELECT
QUOTENAME(c.name) AS "@name"
FROM sys.service_contract_usages cuse
JOIN sys.service_contracts c ON cuse.service_contract_id = c.service_contract_id
WHERE cuse.service_id = svc.service_id
FOR XML PATH('contract'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 17
AND prop.major_id = svc.service_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.services svc
JOIN sys.service_queues q ON svc.service_queue_id = q.object_id
JOIN sys.schemas qs ON q.schema_id = qs.schema_id
WHERE svc.name NOT LIKE 'http://schemas.microsoft.com/%'
FOR XML PATH('service'), TYPE),
(SELECT
QUOTENAME(en.name) AS "@name",
(SELECT
QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = en.principal_id) AS "@owner",
en.service_name AS "@broker-service",
en.broker_instance AS "@broker-instance",
(SELECT
QUOTENAME(subjs.name) AS "@schema",
QUOTENAME(subj.name) AS "@name"
FROM sys.objects subj
JOIN sys.schemas subjs ON subj.schema_id = subjs.schema_id
WHERE subj.object_id = en.parent_id
FOR XML PATH('subject'), TYPE),
-- Event groups
(SELECT DISTINCT
eg.event_group_type_desc AS "@event-group"
FROM @EventGroups eg
WHERE eg.object_id = en.object_id
FOR XML PATH('on'), TYPE),
-- Events
(SELECT
ev.type_desc AS "@event",
(SELECT
eg.event_group_type_desc
FROM @EventGroups eg
WHERE eg.object_id = ev.object_id
AND eg.type = ev.type) AS "@as-part-of"
FROM sys.events ev
WHERE ev.object_id = en.object_id
FOR XML PATH('on'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = en.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.event_notifications en
FOR XML PATH('event-notification'), TYPE)
FOR XML PATH(''), TYPE);
WITH
DataSpaceHelper AS (
SELECT
o.object_id,
i.index_id,
i.type,
QUOTENAME(ds.name) AS "name",
(SELECT
QUOTENAME(c.name) AS "name"
FROM sys.index_columns ic
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.partition_ordinal > 0
AND ds.type = 'PS'
ORDER BY ic.partition_ordinal
FOR XML RAW('partition-column'), TYPE) AS partition_columns
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE (ds.type <> 'FG' OR ds.is_default <> 1)
),
KeyConstraintsHelper AS (
SELECT
kc.type AS type,
QUOTENAME(s.name) AS "schema",
CASE
WHEN kc.is_system_named = 0 THEN QUOTENAME(kc.name)
END AS name,
QUOTENAME(t.name) AS "table",
CASE
WHEN i.type = 1 THEN 'clustered'
END AS "clustered",
CASE
WHEN i.fill_factor > 0 THEN i.fill_factor
END AS "fill-factor",
CASE
WHEN i.is_padded > 0 THEN 'padded'
END AS "padded",
CASE
WHEN i.allow_row_locks = 0 THEN 'no-row-locks'
END AS "no-row-locks",
CASE
WHEN i.allow_page_locks = 0 THEN 'no-page-locks'
END AS "no-page-locks",
ds.name AS "stored-on",
ds.partition_columns,
(SELECT
QUOTENAME(c.name) AS "@name",
CASE
WHEN ic.is_descending_key > 0 THEN 'desc'
END AS "@desc"
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.key_ordinal >= 1
ORDER BY ic.key_ordinal
FOR XML PATH('column'), TYPE) AS "key_columns",
(SELECT
QUOTENAME(c.name) AS "@name",
CASE
WHEN ic.is_included_column > 0 THEN 'included'
END AS "@included"
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.key_ordinal = 0
FOR XML PATH('column'), TYPE) AS "other_columns"
FROM sys.key_constraints kc
JOIN sys.schemas s ON kc.schema_id = s.schema_id
JOIN sys.tables t ON kc.parent_object_id = t.object_id
JOIN sys.indexes i
ON kc.parent_object_id = i.object_id
AND kc.unique_index_id = i.index_id
LEFT JOIN DataSpaceHelper ds
ON ds.object_id = i.object_id
AND ds.index_id = i.index_id
),
ReferenceDependencies AS (
SELECT DISTINCT
deps.object_id AS "referencer",
QUOTENAME(rs.name) AS "schema",
QUOTENAME(reffed.name) AS "name"
FROM sys.sql_dependencies deps
JOIN sys.objects reffed ON deps.referenced_major_id = reffed.object_id
JOIN sys.schemas rs ON reffed.schema_id = rs.schema_id
WHERE deps."class" IN (0, 1)
AND reffed.type IN ('P', 'FN', 'IF', 'TF', 'V')
),
PermissionTarget AS (
SELECT
0 AS "class",
0 AS major_id,
0 AS minor_id,
'DATABASE' AS "class_desc",
NULL AS "class_specifier",
NULL AS "schema_name",
NULL AS "object_name",
NULL AS "column_name"
UNION
SELECT
1,
o.object_id,
0,
'OBJECT',
NULL,
s.name,
o.name,
NULL
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
UNION
SELECT
1,
c.object_id,
c.column_id,
'COLUMN',
NULL,
s.name,
o.name,
c.name
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
UNION
SELECT
3,
s.schema_id,
0,
'SCHEMA',
'SCHEMA',
NULL,
s.name,
NULL
FROM sys.schemas s
UNION
SELECT
4, -- class
r.principal_id, -- major_id
0, -- minor_id
'ROLE', -- class description
'ROLE', -- class specifier
NULL, -- schema_name
r.name, -- object_name
NULL -- column_name
FROM sys.database_principals r
WHERE r.type = 'R'
UNION
SELECT
5, -- class
a.assembly_id, -- major_id
0, -- minor_id
'ASSEMBLY', -- class description
'ASSEMBLY', -- class specifier
NULL, -- schema_name
a.name, -- object_name
NULL -- column_name
FROM sys.assemblies a
UNION
SELECT
6, -- class
t.user_type_id, -- major_id
0, -- minor_id
'TYPE', -- class description
'TYPE', -- class specifier
s.name, -- schema_name
t.name, -- object_name
NULL -- column_name
FROM sys.types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
UNION
SELECT
10, -- class
xsc.xml_collection_id, -- major_id
0, -- minor_id
'XML_SCHEMA_COLLECTION', -- class description
'XML SCHEMA COLLECTION', -- class specifier
s.name, -- schema_name
xsc.name, -- object_name
NULL -- column_name
FROM sys.xml_schema_collections xsc
JOIN sys.schemas s ON xsc.schema_id = s.schema_id
),
Permissions AS (
SELECT
subject.state AS "type",
QUOTENAME(subject.name) AS "@to",
(SELECT
p.permission_name AS "name",
t.class_desc AS "target-type",
t.class_specifier AS "name-scope",
QUOTENAME(t.object_name) AS "on",
QUOTENAME(t.column_name) AS "column",
QUOTENAME(t.schema_name) AS "in-schema",
QUOTENAME(grantor.name) AS "by",
CASE
WHEN p.state = 'W' THEN 'with-grant-option'
END AS "with-grant-option"
FROM sys.database_permissions p
JOIN PermissionTarget t ON p.class = t.class AND p.major_id = t.major_id AND p.minor_id = t.minor_id
LEFT JOIN sys.database_principals grantor ON p.grantor_principal_id = grantor.principal_id
WHERE p.grantee_principal_id = subject.grantee_principal_id
AND p.state = subject.state
AND (p.class <> 4 OR (
SELECT dp.type FROM sys.database_principals dp
WHERE dp.principal_id = p.major_id
) = 'R')
AND (p.class <> 1 OR p.major_id IN (
SELECT o.object_id FROM sys.objects o
))
FOR XML RAW('permission'), TYPE) AS "permissions"
FROM (
SELECT DISTINCT
p.grantee_principal_id,
pi.name,
p.state
FROM sys.database_permissions p
JOIN sys.database_principals pi ON p.grantee_principal_id = pi.principal_id
WHERE p.class IN (SELECT "class" FROM PermissionTarget)
AND pi.type = 'R' -- limit to ROLEs
) subject
),
TypeFields AS (
SELECT
t.user_type_id AS type_id,
s.name AS schema_name,
t.name AS name,
CASE
WHEN s.name = 'sys' AND t.name IN ('binary', 'char', 'varbinary', 'varchar') THEN 1
WHEN s.name = 'sys' AND t.name IN ('nchar', 'nvarchar') THEN 2
END AS max_length_divisor,
CASE
WHEN s.name = 'sys' AND t.name IN ('decimal', 'numeric') THEN 1
END AS scaled_integral
FROM sys.types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
),
SpatialDensities AS (
SELECT
i.object_id,
i.index_id,
(SELECT
(SELECT 1 AS "@n", i.l1_grid AS "@density" FOR XML PATH('grid-level')),
(SELECT 2 AS "@n", i.l2_grid AS "@density" FOR XML PATH('grid-level')),
(SELECT 3 AS "@n", i.l3_grid AS "@density" FOR XML PATH('grid-level')),
(SELECT 4 AS "@n", i.l4_grid AS "@density" FOR XML PATH('grid-level'))
FOR XML PATH(''), TYPE) AS grid_densities
FROM @SpatialIndexInfo i
)
SELECT
-- Database extended properties
(SELECT
p.name AS "@name",
SQL_VARIANT_PROPERTY(p.value, 'BaseType') AS "@type",
p.value AS "text()"
FROM sys.extended_properties p
WHERE p.class = 0
FOR XML PATH('property'), TYPE),
-- Filegroups
@filegroups,
-- Partition functions
@partition_functions,
-- Partition schemes
@partition_schemes,
-- Schemas
@schemas,
-- Roles
@roles,
-- Assemblies
@assemblies,
-- Synonyms
@synonyms,
-- XML Schema Collections
@schema_collections,
-- User defined scalar types
@sql_scalar_types,
-- User defined CLR types
@clr_types,
-- User defined table types (sys.table_types, not available until SQL Server 2008)
@table_types,
-- Fulltext document types
@fulltext_doctypes,
-- Fulltext catalogs
@fulltext_catalogs,
-- Tables
@tables,
-- Default constraints
@default_constraints,
-- Primary key and unique constraints
@primary_keys,
@unique_constraints,
-- Check constraints
@check_constraints,
-- Rules on table columns and alias data types (attach with sp_bindrule)
(SELECT
QUOTENAME(rs.name) AS "@schema",
QUOTENAME(r.name) AS "@name",
sql.definition AS "condition",
(SELECT
QUOTENAME(ts.name) AS "@schema",
QUOTENAME(t.name) AS "@table",
QUOTENAME(c.name) AS "@name"
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas ts ON t.schema_id = ts.schema_id
WHERE c.rule_object_id = r.object_id
FOR XML PATH('column'), TYPE),
(SELECT
QUOTENAME(ts.name) AS "@schema",
QUOTENAME(t.name) AS "@name"
FROM sys.types t
JOIN sys.schemas ts ON t.schema_id = ts.schema_id
WHERE t.rule_object_id = r.object_id
FOR XML PATH('type'), TYPE),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = r.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.objects r
JOIN sys.schemas rs ON r.schema_id = rs.schema_id
JOIN sys.sql_modules sql ON r.object_id = sql.object_id
WHERE r.object_id IN (
SELECT c.rule_object_id FROM sys.columns c
UNION
SELECT t.rule_object_id FROM sys.types t
)
FOR XML PATH('rule'), TYPE),
-- Foreign key constraints (create with: ALTER TABLE schema.table ADD [CONSTRAINT name] FOREIGN KEY ...)
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(t.name) AS "@table",
CASE
WHEN fk.is_system_named = 0 THEN QUOTENAME(fk.name)
END AS "@name",
REPLACE(fk.delete_referential_action_desc, '_', ' ') AS "@on-delete",
REPLACE(fk.update_referential_action_desc, '_', ' ') AS "@on-update",
CASE
WHEN fk.is_disabled <> 0 THEN 'disabled'
END AS "@disabled",
QUOTENAME(rs.name) AS "referent/@schema",
QUOTENAME(r.name) AS "referent/@name",
(SELECT
QUOTENAME(fc.name) AS "@from",
QUOTENAME(tc.name) AS "@to"
FROM sys.foreign_key_columns fkc
JOIN sys.columns fc
ON fkc.parent_object_id = fc.object_id
AND fkc.parent_column_id = fc.column_id
JOIN sys.columns tc
ON fkc.referenced_object_id = tc.object_id
AND fkc.referenced_column_id = tc.column_id
WHERE fkc.constraint_object_id = fk.object_id
ORDER BY fkc.constraint_column_id
FOR XML PATH('link'), TYPE),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = fk.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.foreign_keys fk
JOIN sys.tables t ON fk.parent_object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.objects r ON fk.referenced_object_id = r.object_id
JOIN sys.schemas rs ON r.schema_id = rs.schema_id
FOR XML PATH('foreign-key'), TYPE),
-- DDL triggers
(SELECT
QUOTENAME(tgr.name) AS "@name",
CASE
WHEN sql.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = sql.execute_as_principal_id
)
END AS "@execute-as",
CASE
WHEN tgr.is_disabled > 0 THEN 'disabled'
END AS "@disabled",
-- Event groups
(SELECT DISTINCT
eg.event_group_type_desc AS "@name"
FROM @EventGroups eg
WHERE eg.object_id = tgr.object_id
FOR XML PATH('event-group'), TYPE),
-- Events
(SELECT
ev.type_desc AS "@type",
(SELECT
eg.event_group_type_desc
FROM @EventGroups eg
WHERE eg.object_id = ev.object_id
AND eg.type = ev.type) AS "@from-group"
FROM sys.events ev
WHERE ev.object_id = tgr.object_id
FOR XML PATH('event'), TYPE),
-- Definition
sql.definition AS "do",
-- Dependencies
(SELECT
rd."schema" AS "@schema",
rd."name" AS "@name"
FROM ReferenceDependencies rd
WHERE rd.referencer = tgr.object_id
FOR XML PATH('references'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = tgr.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.triggers tgr
JOIN sys.sql_modules sql ON tgr.object_id = sql.object_id
WHERE tgr.parent_id = 0
AND tgr.type = 'TR'
FOR XML PATH('ddl-trigger'), TYPE),
(SELECT
QUOTENAME(tgr.name) AS "@name",
CASE
WHEN asmmod.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = asmmod.execute_as_principal_id
)
END AS "@execute-as",
CASE
WHEN tgr.is_disabled > 0 THEN 'disabled'
ELSE NULL
END AS "@disabled",
-- Event groups
(SELECT DISTINCT
eg.event_group_type_desc AS "@name"
FROM @EventGroups eg
WHERE eg.object_id = tgr.object_id
FOR XML PATH('event-group'), TYPE),
-- Events
(SELECT
ev.type_desc AS "@type",
(SELECT
eg.event_group_type_desc
FROM @EventGroups eg
WHERE eg.object_id = ev.object_id
AND eg.type = ev.type) AS "@from-group"
FROM sys.events ev
WHERE ev.object_id = tgr.object_id
FOR XML PATH('event'), TYPE),
-- CLR reference
QUOTENAME(asm.name) AS "implementation/@assembly",
QUOTENAME(asmmod.assembly_class) AS "implementation/@class",
QUOTENAME(asmmod.assembly_method) AS "implementation/@method",
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = tgr.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.triggers tgr
JOIN sys.assembly_modules asmmod ON tgr.object_id = asmmod.object_id
JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
WHERE tgr.parent_id = 0
AND tgr.type = 'TA'
FOR XML PATH('ddl-trigger'), TYPE),
-- DML triggers on tables
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(tgr.name) AS "@name",
CASE
WHEN sql.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = sql.execute_as_principal_id
)
END AS "@execute-as",
CASE
WHEN tgr.is_disabled > 0 THEN 'disabled'
END AS "@disabled",
CASE
WHEN tgr.is_not_for_replication > 0 THEN 'not-for-replication'
END AS "@not-for-replication",
CASE
WHEN tgr.is_instead_of_trigger > 0 THEN 'INSTEAD OF'
ELSE 'AFTER'
END AS "@timing",
-- Table
QUOTENAME(ts.name) AS "table/@schema",
QUOTENAME(t.name) AS "table/@name",
-- Events
(SELECT
ev.type_desc AS "@type"
FROM sys.events ev
WHERE ev.object_id = tgr.object_id
FOR XML PATH('event'), TYPE),
-- Definition
sql.definition AS "do",
-- Dependencies
(SELECT
rd."schema" AS "@schema",
rd."name" AS "@name"
FROM ReferenceDependencies rd
WHERE rd.referencer = tgr.object_id
FOR XML PATH('references'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = tgr.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.triggers tgr
JOIN sys.objects o ON tgr.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.tables t ON tgr.parent_id = t.object_id
JOIN sys.schemas ts ON t.schema_id = ts.schema_id
JOIN sys.sql_modules sql ON tgr.object_id = sql.object_id
WHERE tgr.parent_id <> 0
AND tgr.type = 'TR'
FOR XML PATH('dml-trigger'), TYPE),
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(tgr.name) AS "@name",
CASE
WHEN asmmod.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = asmmod.execute_as_principal_id
)
END AS "@execute-as",
CASE
WHEN tgr.is_disabled > 0 THEN 'disabled'
END AS "@disabled",
CASE
WHEN tgr.is_not_for_replication > 0 THEN 'not-for-replication'
END AS "@not-for-replication",
CASE
WHEN tgr.is_instead_of_trigger > 0 THEN 'INSTEAD OF'
ELSE 'AFTER'
END AS "@timing",
-- Table
QUOTENAME(ts.name) AS "table/@schema",
QUOTENAME(t.name) AS "table/@name",
-- Event
(SELECT
ev.type_desc AS "@type"
FROM sys.events ev
WHERE ev.object_id = tgr.object_id
FOR XML PATH('event'), TYPE),
-- CLR reference
QUOTENAME(asm.name) AS "implementation/@assembly",
QUOTENAME(asmmod.assembly_class) AS "implementation/@class",
QUOTENAME(asmmod.assembly_method) AS "implementation/@method",
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = tgr.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.triggers tgr
JOIN sys.objects o ON tgr.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.tables t ON tgr.parent_id = t.object_id
JOIN sys.schemas ts ON t.schema_id = ts.schema_id
JOIN sys.assembly_modules asmmod ON tgr.object_id = asmmod.object_id
JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
WHERE tgr.parent_id <> 0
AND tgr.type = 'TA'
FOR XML PATH('dml-trigger'), TYPE),
-- Indexes (but not ones used for constraints)
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(rel.name) AS "@relation",
QUOTENAME(i.name) AS "@name",
CASE
WHEN i.is_unique > 0 THEN 'unique'
END AS "@unique",
CASE
WHEN i.ignore_dup_key > 0 THEN 'ignore-duplicates'
END AS "@ignore-duplicates",
CASE
WHEN i.is_padded > 0 THEN 'padded'
END AS "@padded",
CASE
WHEN i.is_disabled > 0 THEN 'disabled'
END AS "@disabled",
CASE
WHEN i.allow_row_locks = 0 THEN 'no-row-locks'
END AS "@no-row-locks",
CASE
WHEN i.allow_page_locks = 0 THEN 'no-page-locks'
END AS "@no-page-locks",
CASE
WHEN i.fill_factor > 0 THEN i.fill_factor
END AS "@fill-factor",
spatial.spatial_index_type_desc AS "@spatial-index-over",
spatial.cells_per_object AS "@cells-per-object",
ds.name AS "@stored-on",
spatial.bb_xmin AS "bounds/@xmin",
spatial.bb_ymin AS "bounds/@ymin",
spatial.bb_xmax AS "bounds/@xmax",
spatial.bb_ymax AS "bounds/@ymax",
gd.grid_densities,
-- Key columns
(SELECT
QUOTENAME(c.name) AS "@name",
CASE
WHEN ic.is_descending_key > 0 THEN 'desc'
END AS "@desc"
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.key_ordinal >= 1
ORDER BY ic.key_ordinal
FOR XML PATH('column'), TYPE) AS "*",
-- Included and XML columns
(SELECT
QUOTENAME(c.name) AS "@name",
CASE
WHEN ic.is_included_column > 0 THEN 'included'
END AS "@included"
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.key_ordinal = 0
FOR XML PATH('column'), TYPE) AS "*",
ds.partition_columns AS "*",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 7
AND prop.major_id = i.object_id
AND prop.minor_id = i.index_id
FOR XML PATH('property'), TYPE)
FROM sys.indexes i
JOIN sys.objects rel ON i.object_id = rel.object_id
JOIN sys.schemas s ON rel.schema_id = s.schema_id
LEFT JOIN DataSpaceHelper ds
ON i.object_id = ds.object_id
AND i.index_id = ds.index_id
LEFT JOIN @SpatialIndexInfo spatial
ON i.object_id = spatial.object_id
AND i.index_id = spatial.index_id
LEFT JOIN SpatialDensities gd
ON i.object_id = gd.object_id
AND i.index_id = gd.index_id
WHERE CAST(i.is_primary_key AS INT) + CAST(i.is_unique_constraint AS INT) = 0
AND i.is_hypothetical = 0 -- These are only for column statistics
AND i.type <> 0 -- Exclude HEAP
AND s.name <> N'sys'
FOR XML PATH('index'), TYPE),
-- Fulltext indexes
(SELECT
QUOTENAME(s.name) as "@schema",
QUOTENAME(t.name) as "@table",
QUOTENAME(ki.name) as "@key-index",
QUOTENAME(cat.name) as "@catalog",
CASE
WHEN i.change_tracking_state = 'M' THEN 'manual'
WHEN i.change_tracking_state = 'O' THEN 'off' -- NOTE: Will still populate unless NO POPULATION is specified
ELSE 'auto'
END as "@change-tracking",
(SELECT
QUOTENAME(c.name) AS "column",
QUOTENAME(tc.name) AS "type-column",
COALESCE(l.[alias], CAST(ic.language_id AS VARCHAR(20))) AS "language"
FROM sys.fulltext_index_columns ic
JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
LEFT JOIN sys.columns tc
ON ic.object_id = tc.object_id
AND ic.type_column_id = tc.column_id
LEFT JOIN sys.syslanguages l ON ic.language_id = l.lcid
WHERE ic.object_id = i.object_id
FOR XML RAW('column'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = i.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.fulltext_indexes i
JOIN sys.objects t ON i.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.indexes ki ON i.unique_index_id = ki.index_id
LEFT JOIN sys.fulltext_catalogs cat ON i.fulltext_catalog_id = cat.fulltext_catalog_id
FOR XML PATH('fulltext-index'), TYPE),
-- Statistics
(SELECT
QUOTENAME(o.name) AS "@on",
QUOTENAME(os.name) AS "@in-schema",
QUOTENAME(so.name) AS "@name",
CASE
WHEN so.no_recompute > 0 THEN 'no-recompute'
END AS "@no-recompute",
(SELECT
QUOTENAME(c.name) AS "@name"
FROM sys.stats_columns sc
JOIN sys.columns c
ON sc.object_id = c.object_id
AND sc.column_id = c.column_id
WHERE sc.object_id = so.object_id
AND sc.stats_id = so.stats_id
ORDER BY sc.stats_column_id
FOR XML PATH('column'), TYPE),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = so.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.stats so
JOIN sys.objects o ON so.object_id = o.object_id
JOIN sys.schemas os ON o.schema_id = os.schema_id
WHERE so.user_created <> 0
FOR XML PATH('statistics'), TYPE),
-- Views
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(v.name) AS "@name",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = v.principal_id) AS "@owner",
CASE
WHEN sql.definition IS NULL THEN 'encrypted'
END AS "@encrypted",
sql.definition AS "definition",
-- Dependencies
(SELECT
rd."schema" AS "@schema",
rd."name" AS "@name"
FROM ReferenceDependencies rd
WHERE rd.referencer = v.object_id
FOR XML PATH('references'), TYPE),
-- Instead-of triggers
(SELECT
-- Events
(SELECT
ev.type_desc
FROM sys.events ev
WHERE ev.object_id = tgr.object_id
FOR XML PATH('')) AS "@action",
CASE
WHEN sqltgr.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(tgrowner.name)
FROM sys.database_principals tgrowner
WHERE tgrowner.principal_id = sqltgr.execute_as_principal_id
)
END AS "@execute-as",
CASE
WHEN tgr.is_disabled > 0 THEN 'disabled'
END AS "@disabled",
CASE
WHEN tgr.is_not_for_replication > 0 THEN 'not-for-replication'
END AS "@not-for-replication",
QUOTENAME(ts.name) AS "trigger/@schema",
QUOTENAME(tgr.name) AS "trigger/@name",
-- Definition
sqltgr.definition AS "do",
-- Dependencies
(SELECT
rdtgr."schema" AS "@schema",
rdtgr."name" AS "@name"
FROM ReferenceDependencies rdtgr
WHERE rdtgr.referencer = tgr.object_id
FOR XML PATH('references')),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = tgr.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.triggers tgr
JOIN sys.objects tgrobj ON tgr.object_id = tgrobj.object_id
JOIN sys.schemas ts ON tgrobj.schema_id = ts.schema_id
JOIN sys.sql_modules sqltgr ON tgr.object_id = sqltgr.object_id
WHERE tgr.parent_id = v.object_id
FOR XML PATH('instead-of'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = v.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id
JOIN sys.sql_modules sql ON v.object_id = sql.object_id
WHERE v.is_date_correlation_view = 0
FOR XML PATH('view'), TYPE),
-- Stored procedures
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(sp.name) AS "@name",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = sp.principal_id) AS "@owner",
CASE
WHEN sql.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = sql.execute_as_principal_id
)
END AS "@execute-as",
CASE
WHEN sql.definition IS NULL THEN 'encrypted'
END AS "@encrypted",
(SELECT
rd."schema" AS "@schema",
rd."name" AS "@name"
FROM ReferenceDependencies rd
WHERE rd.referencer = sp.object_id
FOR XML PATH('references'), TYPE),
sql.definition as "definition",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = sp.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE),
(SELECT
param.name AS "@param",
prop.name AS "@property",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
JOIN sys.parameters param
ON prop.major_id = param.object_id
AND prop.minor_id = param.parameter_id
WHERE prop.class = 1
AND prop.major_id = sp.object_id
AND prop.minor_id <> 0
FOR XML PATH('param-property'), TYPE)
FROM sys.procedures sp
JOIN sys.schemas s ON sp.schema_id = s.schema_id
JOIN sys.sql_modules sql ON sp.object_id = sql.object_id
FOR XML PATH('stored-procedure'), TYPE),
-- CLR stored procedures
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(sproc.name) AS "@name",
CASE
WHEN asmmod.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = asmmod.execute_as_principal_id
)
END AS "@execute-as",
QUOTENAME(asm.name) AS "implementation/@assembly",
QUOTENAME(asmmod.assembly_class) AS "implementation/@class",
QUOTENAME(asmmod.assembly_method) AS "implementation/@method",
-- Parameters
(SELECT
param.name AS "@name",
CASE
WHEN pt.schema_name <> 'sys' THEN QUOTENAME(pt.schema_name)
END AS "@type-schema",
QUOTENAME(pt.name) AS "@type",
CASE
WHEN param.max_length = -1 AND pt.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(param.max_length / pt.max_length_divisor AS VARCHAR(8))
END AS "@capacity",
param.precision * pt.scaled_integral AS "@precision",
param.scale * pt.scaled_integral AS "@scale",
CASE
WHEN param.is_xml_document > 0 THEN 'full-xml-document'
END AS "@full-xml-document",
(SELECT
QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = param.xml_collection_id) AS "@xml_collection_id",
CASE
WHEN param.is_output <> 0 THEN 'output'
END AS "@output",
param.default_value AS "@default-value",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 2
AND prop.major_id = param.object_id
AND prop.minor_id = param.parameter_id
FOR XML PATH('property'), TYPE)
FROM sys.parameters param
LEFT JOIN TypeFields pt ON param.user_type_id = pt.type_id
WHERE param.object_id = sproc.object_id
ORDER BY param.parameter_id
FOR XML PATH('parameter'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = sproc.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.objects sproc
JOIN sys.assembly_modules asmmod ON sproc.object_id = asmmod.object_id
JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
JOIN sys.schemas s ON sproc.schema_id = s.schema_id
WHERE sproc.type = 'PC'
FOR XML PATH('clr-stored-procedure'), TYPE),
-- User defined functions
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(fn.name) AS "@name",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = fn.principal_id) AS "@owner",
CASE
WHEN sql.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = sql.execute_as_principal_id
)
END AS "@execute-as",
CASE
WHEN sql.definition IS NULL THEN 'encrypted'
END AS "@encrypted",
(SELECT
rd."schema" AS "@schema",
rd."name" AS "@name"
FROM ReferenceDependencies rd
WHERE rd.referencer = fn.object_id
FOR XML PATH('references'), TYPE),
sql.definition as "definition",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = fn.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE),
(SELECT
param.name AS "@param",
prop.name AS "@property",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
JOIN sys.parameters param
ON prop.major_id = param.object_id
AND prop.minor_id = param.parameter_id
WHERE prop.class = 1
AND prop.major_id = fn.object_id
AND prop.minor_id <> 0
FOR XML PATH('param-property'), TYPE)
FROM sys.objects fn
JOIN sys.schemas s ON fn.schema_id = s.schema_id
JOIN sys.sql_modules sql ON fn.object_id = sql.object_id
WHERE fn.type in ('FN', 'IF', 'TF')
FOR XML PATH('user-defined-function'), TYPE),
-- CLR scalar functions
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(fn.name) AS "@name",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = fn.principal_id) AS "@owner",
CASE
WHEN asmmod.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = asmmod.execute_as_principal_id
)
END AS "@execute-as",
QUOTENAME(asm.name) AS "implementation/@assembly",
QUOTENAME(asmmod.assembly_class) AS "implementation/@class",
QUOTENAME(asmmod.assembly_method) AS "implementation/@method",
-- Parameters
(SELECT
param.name AS "@name",
CASE
WHEN pt.schema_name <> 'sys' THEN QUOTENAME(pt.schema_name)
END AS "@type-schema",
QUOTENAME(pt.name) AS "@type",
CASE
WHEN param.max_length = -1 AND pt.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(param.max_length / pt.max_length_divisor AS VARCHAR(8))
END AS "@capacity",
param.precision * pt.scaled_integral AS "@precision",
param.scale * pt.scaled_integral AS "@scale",
CASE
WHEN param.is_xml_document > 0 THEN 'full-xml-document'
END AS "@full-xml-document",
(SELECT
QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = param.xml_collection_id) AS "@xml_collection",
param.default_value AS "@default-value",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 2
AND prop.major_id = param.object_id
AND prop.minor_id = param.parameter_id
FOR XML PATH('property'), TYPE)
FROM sys.parameters param
LEFT JOIN TypeFields pt ON param.user_type_id = pt.type_id
WHERE param.object_id = fn.object_id
AND param.parameter_id > 0
ORDER BY param.parameter_id
FOR XML PATH('parameter'), TYPE),
-- Return type
(SELECT
CASE
WHEN rt.schema_name <> 'sys' THEN QUOTENAME(rt.schema_name)
END AS "@type-schema",
QUOTENAME(rt.name) AS "@type",
CASE
WHEN param.max_length = -1 AND rt.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(param.max_length / rt.max_length_divisor AS VARCHAR(8))
END AS "@capacity",
param.precision * rt.scaled_integral AS "@precision",
param.scale * rt.scaled_integral AS "@scale",
CASE
WHEN param.is_xml_document > 0 THEN 'full-xml-document'
END AS "@full-xml-document",
(SELECT
QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = param.xml_collection_id) AS "@xml_collection"
FROM sys.parameters param
LEFT JOIN TypeFields rt ON param.user_type_id = rt.type_id
WHERE param.object_id = fn.object_id
AND param.parameter_id = 0
FOR XML PATH('returns'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = fn.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.objects fn
JOIN sys.schemas s ON fn.schema_id = s.schema_id
JOIN sys.assembly_modules asmmod ON fn.object_id = asmmod.object_id
JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
WHERE fn.type = 'FS'
FOR XML PATH('clr-function'), TYPE),
-- CLR table valued functions
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(fn.name) AS "@name",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = fn.principal_id) AS "@owner",
CASE
WHEN asmmod.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = asmmod.execute_as_principal_id
)
END AS "@execute-as",
QUOTENAME(asm.name) AS "implementation/@assembly",
QUOTENAME(asmmod.assembly_class) AS "implementation/@class",
QUOTENAME(asmmod.assembly_method) AS "implementation/@method",
-- Parameters
(SELECT
param.name AS "@name",
CASE
WHEN pt.schema_name <> 'sys' THEN QUOTENAME(pt.schema_name)
END AS "@type-schema",
QUOTENAME(pt.name) AS "@type",
CASE
WHEN param.max_length = -1 AND pt.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(param.max_length / pt.max_length_divisor AS VARCHAR(8))
END AS "@capacity",
param.precision * pt.scaled_integral AS "@precision",
param.scale * pt.scaled_integral AS "@scale",
CASE
WHEN param.is_xml_document > 0 THEN 'full-xml-document'
END AS "@full-xml-document",
(SELECT
QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = param.xml_collection_id) AS "@xml_collection_id",
param.default_value AS "@default-value",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 2
AND prop.major_id = param.object_id
AND prop.minor_id = param.parameter_id
FOR XML PATH('property'), TYPE)
FROM sys.parameters param
LEFT JOIN TypeFields pt ON param.user_type_id = pt.type_id
WHERE param.object_id = fn.object_id
AND param.parameter_id > 0
ORDER BY param.parameter_id
FOR XML PATH('parameter'), TYPE),
-- Result table
(SELECT
(SELECT
QUOTENAME(c.name) AS "@name",
CASE
WHEN ct.schema_name <> 'sys' THEN QUOTENAME(ct.schema_name)
END AS "@type-schema",
QUOTENAME(ct.name) AS "@type",
CASE
WHEN c.max_length = -1 AND ct.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(c.max_length / ct.max_length_divisor AS VARCHAR(8))
END AS "@capacity",
c.precision * ct.scaled_integral AS "@precision",
c.scale * ct.scaled_integral AS "@scale",
c.collation_name AS "@collation",
CASE
WHEN c.is_ansi_padded = 0 AND ct.schema_name = 'sys' AND ct.name IN ('char', 'varchar', 'binary', 'varbinary') -- but not nchar or nvarchar!
THEN 'not-ansi-padded'
END AS "@not-ansi-padded",
CASE
WHEN c.is_xml_document > 0 THEN 'full-xml-document'
ELSE NULL
END AS "@full-xml-document",
(SELECT
QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = c.xml_collection_id) AS "@xml_collection_id",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = fn.object_id
AND prop.minor_id = c.column_id
FOR XML PATH('property'), TYPE)
FROM sys.columns c
LEFT JOIN TypeFields ct ON c.user_type_id = ct.type_id
WHERE c.object_id = fn.object_id
ORDER BY c.column_id
FOR XML PATH('column'), TYPE)
FOR XML PATH('result-table'), TYPE),
-- Properties
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = fn.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.objects fn
JOIN sys.schemas s ON fn.schema_id = s.schema_id
JOIN sys.assembly_modules asmmod ON fn.object_id = asmmod.object_id
JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
WHERE fn.type = 'FT'
FOR XML PATH('clr-function'), TYPE),
-- CLR aggregate functions
(SELECT
QUOTENAME(s.name) AS "@schema",
QUOTENAME(fn.name) AS "@name",
(SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = fn.principal_id) AS "@owner",
CASE
WHEN asmmod.execute_as_principal_id = -2 THEN 'OWNER'
ELSE (
SELECT QUOTENAME(p.name)
FROM sys.database_principals p
WHERE p.principal_id = asmmod.execute_as_principal_id
)
END AS "@execute-as",
QUOTENAME(asm.name) AS "implementation/@assembly",
QUOTENAME(asmmod.assembly_class) AS "implementation/@class",
(SELECT
param.name AS "@name",
CASE
WHEN pt.schema_name <> 'sys' THEN QUOTENAME(pt.schema_name)
END AS "@type-schema",
QUOTENAME(pt.name) AS "@type",
CASE
WHEN param.max_length = -1 AND pt.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(param.max_length / pt.max_length_divisor AS VARCHAR(8))
END AS "@capacity",
param.precision * pt.scaled_integral AS "@precision",
param.scale * pt.scaled_integral AS "@scale",
CASE
WHEN param.is_xml_document > 0 THEN 'full-xml-document'
END AS "@full-xml-document",
(SELECT
QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = param.xml_collection_id) AS "@xml_collection",
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 2
AND prop.major_id = param.object_id
AND prop.minor_id = param.parameter_id
FOR XML PATH('property'), TYPE)
FROM sys.parameters param
LEFT JOIN TypeFields pt ON param.user_type_id = pt.type_id
WHERE param.object_id = fn.object_id
AND param.parameter_id > 0
ORDER BY param.parameter_id
FOR XML PATH('parameter'), TYPE),
-- Return type
(SELECT
CASE
WHEN rt.schema_name <> 'sys' THEN QUOTENAME(rt.schema_name)
END AS "@type-schema",
QUOTENAME(rt.name) AS "@type",
CASE
WHEN param.max_length = -1 AND rt.max_length_divisor IS NOT NULL THEN 'max'
ELSE CAST(param.max_length / rt.max_length_divisor AS VARCHAR(8))
END AS "@capacity",
param.precision * rt.scaled_integral AS "@precision",
param.scale * rt.scaled_integral AS "@scale",
CASE
WHEN param.is_xml_document > 0 THEN 'full-xml-document'
END AS "@full-xml-document",
(SELECT
QUOTENAME(xss.name) + '.' + QUOTENAME(xs.name)
FROM sys.xml_schema_collections xs
JOIN sys.schemas xss ON xs.schema_id = xss.schema_id
WHERE xs.xml_collection_id = param.xml_collection_id) AS "@xml_collection"
FROM sys.parameters param
LEFT JOIN TypeFields rt ON param.user_type_id = rt.type_id
WHERE param.object_id = fn.object_id
AND param.parameter_id = 0
FOR XML PATH('returns'), TYPE),
(SELECT
prop.name AS "@name",
SQL_VARIANT_PROPERTY(prop.value, 'BaseType') AS "@type",
prop.value AS "text()"
FROM sys.extended_properties prop
WHERE prop.class = 1
AND prop.major_id = fn.object_id
AND prop.minor_id = 0
FOR XML PATH('property'), TYPE)
FROM sys.objects fn
JOIN sys.schemas s ON fn.schema_id = s.schema_id
JOIN sys.assembly_modules asmmod ON fn.object_id = asmmod.object_id
JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
WHERE fn.type = 'AF'
FOR XML PATH('clr-aggregate'), TYPE),
-- Service Broker information
@service_broker_details,
-- Role permissions
(SELECT
p."@to",
p."permissions" AS "*"
FROM Permissions p
WHERE p.type IN ('G', 'W')
FOR XML PATH('granted'), TYPE),
(SELECT
p."@to",
p."permissions" AS "*"
FROM Permissions p
WHERE p.type IN ('D')
FOR XML PATH('denied'), TYPE),
(SELECT
p."@to" AS "@from",
p."permissions" AS "*"
FROM Permissions p
WHERE p.type IN ('R')
FOR XML PATH('revoked'), TYPE),
-- FOR CONSITENCY DURING DEVELOPMENT
NULL AS "end"
FOR XML RAW('database');
/*
Types requiring length:
- binary
- char
- nchar
- nvarchar
- varbinary
- varchar
Types requiring precision and scale:
- decimal
- numeric
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment