Last active
February 26, 2018 07:29
-
-
Save rtweeks/62d8fb9c6ca3de1195d9 to your computer and use it in GitHub Desktop.
MSSQL Database structure extractor
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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