Extraction Queries for SQL Server¶
Alation Cloud Service Applies to Alation Cloud Service instances of Alation
Customer Managed Applies to customer-managed instances of Alation
Catalog¶
Ensure the query has a column labeled as CATALOG
in the SELECT
statement.
SELECT
name AS 'CATALOG'
FROM
sys.databases
WHERE
name NOT IN ('''')
AND name NOT IN ( 'msdb' , 'model' , 'resource' , 'tempdb');
Schema¶
Ensure the query has a column labeled as CATALOG
, SCHEMA
, and REMARKS
in the SELECT
statement.
SELECT
iss.CATALOG_NAME AS 'CATALOG' ,
iss.SCHEMA_NAME AS 'SCHEMA',
SEP.value AS 'REMARKS'
FROM
INFORMATION_SCHEMA.SCHEMATA iss
JOIN sys.schemas s ON
iss.SCHEMA_NAME = s.name
LEFT JOIN sys.extended_properties SEP ON
s.schema_id = SEP.major_id
AND SEP.minor_id = 0
WHERE
CONCAT(CATALOG_NAME,
'.',
SCHEMA_NAME)
NOT IN ('''')
AND SCHEMA_NAME NOT IN ( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');
Table¶
Ensure the query has columns labeled as TABLE_NAME
, CREATED_DATE
, ALTER_TIME
, SCHEMA
, TABLE_SIZE
, TABLE_TYPE
, REMARKS
, and CATALOG
in the SELECT
statement.
SELECT
t.name AS TABLE_NAME,
t.create_date AS CREATED_DATE,
t.modify_date AS ALTER_TIME,
s.name AS 'SCHEMA',
SUM(a.used_pages) AS TABLE_SIZE,
CASE
WHEN t.type = 'AF' THEN 'Aggregate function (CLR)'
WHEN t.type = 'C' THEN 'CHECK constraint'
WHEN t.type = 'D' THEN 'DEFAULT (constraint or stand-alone)'
WHEN t.type = 'F' THEN 'FOREIGN KEY constraint'
WHEN t.type = 'FN' THEN 'SQL scalar function'
WHEN t.type = 'FS' THEN 'Assembly (CLR) scalar-function'
WHEN t.type = 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN t.type = 'IF' THEN 'SQL inline table-valued function'
WHEN t.type = 'IT' THEN 'INTERNAL TABLE'
WHEN t.type = 'P' THEN 'SQL Stored Procedure'
WHEN t.type = 'PC' THEN 'Assembly (CLR) stored-procedure'
WHEN t.type = 'PG' THEN 'Plan guide'
WHEN t.type = 'PK' THEN 'PRIMARY KEY constraint'
WHEN t.type = 'R' THEN 'Rule (old-style, stand-alone)'
WHEN t.type = 'RF' THEN 'Replication-filter-procedure'
WHEN t.type = 'S' THEN 'SYSTEM TABLE'
WHEN t.type = 'SN' THEN 'Synonym'
WHEN t.type = 'SO' THEN 'Sequence object'
WHEN t.type = 'U' THEN 'TABLE'
WHEN t.type = 'V' THEN 'VIEW'
WHEN t.type = 'EC' THEN 'Edge constraint'
WHEN t.type = 'SQ' THEN 'Service queue'
WHEN t.type = 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN t.type = 'TF' THEN 'SQL table-valued-function'
WHEN t.type = 'TR' THEN 'SQL DML trigger'
WHEN t.type = 'TT ' THEN 'Table type'
WHEN t.type = 'UQ' THEN 'UNIQUE constraint'
WHEN t.type = 'X' THEN 'Extended stored procedure'
ELSE t.type
END AS 'TABLE_TYPE',
CONVERT(VARCHAR(MAX),
SEP.value) AS REMARKS,
DB_NAME() AS 'CATALOG'
FROM
sys.objects t
LEFT OUTER JOIN sys.indexes i ON
t.object_id = i.object_id
LEFT OUTER JOIN sys.partitions p ON
i.object_id = p.object_id
AND i.index_id = p.index_id
LEFT OUTER JOIN sys.allocation_units a ON
p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON
t.schema_id = s.schema_id
LEFT JOIN sys.extended_properties SEP ON
t.object_id = SEP.major_id
AND SEP.minor_id = 0
WHERE
s.name NOT IN
( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables
')
AND CONCAT(DB_NAME(),
'.',
s.name)
NOT IN ('''')
AND t.type IN('U')
GROUP BY
t.name,
s.name,
p.rows,
t.create_date,
t.modify_date,
t.type ,
SEP.value;
View¶
Ensure the query has columns labeled as CATALOG
, SCHEMA
, VIEW_NAME
, VIEW_CREATE_STATEMENT
, 'VIEW' AS VIEW_TYPE
, and REMARKS
in the SELECT
statement.
SELECT
DB_NAME() AS 'CATALOG' ,
SCHEMA_NAME(schema_id) AS 'SCHEMA' ,
v.name AS 'VIEW_NAME' ,
CONVERT(NVARCHAR(MAX),
OBJECT_DEFINITION(object_id)) AS 'VIEW_CREATE_STATEMENT' ,
'VIEW' AS 'VIEW_TYPE' ,
CONVERT(varchar(max),
sep.value) AS 'REMARKS'
FROM
sys.views v
LEFT JOIN sys.extended_properties sep ON
object_id = sep.major_id
AND sep.minor_id = 0
WHERE
CONCAT(DB_NAME(),
'.',
SCHEMA_NAME(schema_id))
NOT IN ('''')
AND SCHEMA_NAME(schema_id) NOT IN ( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');
Column¶
Ensure the query has columns labeled as TABLE_NAME
, SCHEMA
, COLUMN_NAME
, MAX_LENGTH
, PRECISION
, SCALE
, CATALOG
, TYPE_NAME
, DATA_TYPE
, ORDINAL_POSITION
, IS_NULLABLE
, REMARKS
, and COLUMN_DEFAULT
in the SELECT
statement.
SELECT
t.name as TABLE_NAME,
SCHEMA_NAME(schema_id) as 'SCHEMA',
c.name as COLUMN_NAME,
c.max_length as MAX_LENGTH,
c.precision as PRECISION,
c.scale as SCALE,
DB_NAME() as 'CATALOG',
(CASE
WHEN TYPE_NAME(c.user_type_id) = 'char' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
WHEN TYPE_NAME(c.user_type_id) = 'varchar' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
WHEN TYPE_NAME(c.user_type_id) = 'nchar' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length / 2, ')')
WHEN TYPE_NAME(c.user_type_id) = 'nvarchar' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length / 2, ')')
WHEN TYPE_NAME(c.user_type_id) = 'decimal' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.precision, ',', c.scale, ')')
WHEN TYPE_NAME(c.user_type_id) = 'numeric' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.precision, ',', c.scale, ')')
WHEN TYPE_NAME(c.user_type_id) = 'binary' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
WHEN TYPE_NAME(c.user_type_id) = 'varbinary' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
Else TYPE_NAME(c.user_type_id)
END ) AS TYPE_NAME,
TYPE_NAME(c.user_type_id)as DATA_TYPE,
c.column_id as ORDINAL_POSITION,
CONVERT(varchar(max),
sep.value) AS REMARKS,
null as COLUMN_DEF,
CASE
WHEN c.is_nullable = 1 THEN 'true'
ELSE 'false'
END AS IS_NULLABLE,
NULL as COLUMN_DEFAULT
FROM
sys.tables AS t
INNER JOIN sys.columns c ON
t.object_id = c.object_id
LEFT JOIN sys.extended_properties sep ON
t.object_id = sep.major_id
AND c.column_id = sep.minor_id
WHERE
CONCAT(DB_NAME(), '.', SCHEMA_NAME (t.schema_id))
NOT IN ('''')
AND SCHEMA_NAME (t.schema_id) NOT IN ( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables
')
UNION
SELECT
t.name as TABLE_NAME,
SCHEMA_NAME(schema_id) as 'SCHEMA',
c.name as COLUMN_NAME,
c.max_length as MAX_LENGTH,
c.precision as PRECISION,
c.scale as SCALE,
DB_NAME() as 'CATALOG',
(CASE
WHEN TYPE_NAME(c.user_type_id) = 'char' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
WHEN TYPE_NAME(c.user_type_id) = 'varchar' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
WHEN TYPE_NAME(c.user_type_id) = 'nchar' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length / 2, ')')
WHEN TYPE_NAME(c.user_type_id) = 'nvarchar' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length / 2, ')')
WHEN TYPE_NAME(c.user_type_id) = 'decimal' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.precision, ',', c.scale, ')')
WHEN TYPE_NAME(c.user_type_id) = 'numeric' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.precision, ',', c.scale, ')')
WHEN TYPE_NAME(c.user_type_id) = 'binary' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
WHEN TYPE_NAME(c.user_type_id) = 'varbinary' THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
Else TYPE_NAME(c.user_type_id)
END ) AS TYPE_NAME,
TYPE_NAME(c.user_type_id)as DATA_TYPE,
c.column_id as ORDINAL_POSITION,
CONVERT(varchar(max),
sep.value) as REMARKS,
null as COLUMN_DEF,
CASE
WHEN c.is_nullable = 1 THEN 'true'
ELSE 'false'
END AS IS_NULLABLE,
NULL as COLUMN_DEFAULT
FROM
sys.views AS t
INNER JOIN sys.columns c ON
t.object_id = c.object_id
LEFT JOIN sys.extended_properties sep ON
t.object_id = sep.major_id
AND c.column_id = sep.minor_id
WHERE
CONCAT(DB_NAME(), '.', SCHEMA_NAME (t.schema_id))
NOT IN ('''')
AND SCHEMA_NAME (t.schema_id) NOT IN ( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');
Primary Key¶
Ensure the query has columns labeled as CATALOG
, SCHEMA
, TABLE_NAME
, and COLUMN_NAME
in the SELECT
statement.
SELECT
KCU.TABLE_CATALOG AS 'CATALOG',
KCU.TABLE_NAME AS 'TABLE_NAME',
KCU.COLUMN_NAME AS 'COLUMN_NAME',
KCU.TABLE_SCHEMA AS 'SCHEMA'
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON
KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND KCU.TABLE_NAME = TC.TABLE_NAME
WHERE
TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND
CONCAT(KCU.TABLE_CATALOG,
'.',
KCU.TABLE_SCHEMA)
NOT IN ('''')
AND KCU.TABLE_SCHEMA NOT IN
( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');
Foreign Key¶
Ensure the query has columns labeled as PK_CATALOG
, PK_SCHEMA
, PK_TABLE
, PK_COLUMN
, PK_NAME
, FK_CATALOG
, FK_SCHEMA
, FK_TABLE
, FK_COLUMN
, and FK_NAME
in the SELECT statement.
SELECT
KF.TABLE_CATALOG AS FK_CATALOG,
KP.TABLE_CATALOG AS PK_CATALOG,
RC.CONSTRAINT_NAME FK_Name,
KF.TABLE_SCHEMA FK_Schema,
KF.TABLE_NAME FK_Table,
KF.COLUMN_NAME FK_Column,
RC.UNIQUE_CONSTRAINT_NAME PK_Name,
KP.TABLE_SCHEMA PK_Schema,
KP.TABLE_NAME PK_Table,
KP.COLUMN_NAME PK_Column
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON
RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON
RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
KP.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KF.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND CONCAT(KF.TABLE_CATALOG,
'.',
KF.TABLE_SCHEMA)
NOT IN ('''')
AND KF.TABLE_SCHEMA NOT IN
( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');
Function¶
Ensure the query has columns labeled as CATALOG
, SCHEMA
, FUNCTION_NAME
, FUNCTION_DEFINITION
, and REMARKS
in the SELECT
list.
SELECT
isr.SPECIFIC_CATALOG AS 'CATALOG',
isr.SPECIFIC_NAME AS FUNCTION_NAME,
isr.SPECIFIC_SCHEMA AS 'SCHEMA',
sep.value AS REMARKS,
ssm.definition AS FUNCTION_DEFINITION
FROM
INFORMATION_SCHEMA.ROUTINES isr
JOIN
sys.sql_modules ssm
ON
ssm.object_id = object_id(CONCAT(isr.SPECIFIC_CATALOG,
'.',
isr.SPECIFIC_SCHEMA,
'.',
isr.SPECIFIC_NAME))
LEFT JOIN
sys.extended_properties sep
ON
ssm.object_id = sep.major_id
WHERE
CONCAT(SPECIFIC_CATALOG,
'.',
SPECIFIC_SCHEMA)
NOT IN ('''')
AND SPECIFIC_SCHEMA NOT IN ( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');
Function Definition¶
Ensure the query has columns labeled as CATALOG
, SCHEMA
, FUNCTION_NAME
, ARG_NAME
, TYPE_NAME
, ARG_TYPE
, ARG_DEF
, and COLUMN_TYPE
in the SELECT
list.
SELECT
DB_NAME() AS 'CATALOG',
isr.SPECIFIC_SCHEMA AS 'SCHEMA',
isr.SPECIFIC_NAME AS 'FUNCTION_NAME',
isr.ROUTINE_DEFINITION AS 'ARG_DEF',
isp.DATA_TYPE AS 'TYPE_NAME',
isp.DATA_TYPE AS 'ARG_TYPE',
CASE
isp.PARAMETER_NAME
WHEN NULL
THEN '@RETURN_VALUE'
WHEN ''
THEN '@RETURN_VALUE'
ELSE isp.PARAMETER_NAME
END AS 'ARG_NAME',
CASE
WHEN (isp.PARAMETER_MODE = 'OUT'
AND isp.IS_RESULT = 'YES')
THEN 5
WHEN (isp.PARAMETER_MODE = 'OUT'
AND isp.IS_RESULT = 'NO')
THEN 4
WHEN (isp.PARAMETER_MODE = 'IN'
AND isp.IS_RESULT = 'NO')
THEN 1
ELSE 3
END AS COLUMN_TYPE
FROM
INFORMATION_SCHEMA.ROUTINES AS isr
JOIN sys.objects AS so
ON
(type_desc LIKE '%FUNCTION%'
OR type_desc LIKE '%PROCEDURE%')
AND so.name = isr.specific_name
JOIN INFORMATION_SCHEMA.PARAMETERS AS isp
ON
isp.SPECIFIC_SCHEMA = isr.SPECIFIC_SCHEMA
AND isp.SPECIFIC_NAME = isr.SPECIFIC_NAME
WHERE
CONCAT(DB_NAME(),
'.',
isr.SPECIFIC_SCHEMA)
NOT IN ('''')
AND isr.SPECIFIC_SCHEMA NOT IN
( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables
')
UNION
SELECT
DB_NAME() AS 'CATALOG',
isr.SPECIFIC_SCHEMA AS 'SCHEMA',
isr.SPECIFIC_NAME AS 'FUNCTION_NAME',
isr.ROUTINE_DEFINITION AS 'ARG_DEF',
isp.DATA_TYPE AS 'TYPE_NAME',
isp.DATA_TYPE AS 'ARG_TYPE',
'@RETURN_VALUE' AS 'ARG_NAME',
5 AS COLUMN_TYPE
FROM
INFORMATION_SCHEMA.ROUTINES AS isr
JOIN sys.objects AS so
ON
(type_desc LIKE '%FUNCTION%'
OR type_desc LIKE '%PROCEDURE%')
AND so.name = isr.SPECIFIC_NAME
JOIN INFORMATION_SCHEMA.PARAMETERS AS isp
ON
isp.SPECIFIC_SCHEMA = isr.SPECIFIC_SCHEMA
AND isp.SPECIFIC_NAME = isr.SPECIFIC_NAME
AND NOT EXISTS(
SELECT
1
FROM
INFORMATION_SCHEMA.PARAMETERS AS isp
WHERE
isp.IS_RESULT = 'YES'
AND isp.SPECIFIC_NAME = isr.SPECIFIC_NAME
AND isp.SPECIFIC_SCHEMA = isr.SPECIFIC_SCHEMA)
WHERE
CONCAT(DB_NAME(),
'.',
isr.SPECIFIC_SCHEMA)
NOT IN ('''')
AND isr.SPECIFIC_SCHEMA NOT IN
( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');
Synonyms¶
Ensure the query has columns labelled as SYNONYM_CATALOG
, SYNONYM_SCHEMA
, SYNONYM_NAME
, TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, and REMARKS
in the SELECT
statement. The fully qualified name for the synonym base table is required.
SELECT
DB_NAME() AS SYNONYM_CATALOG,
SCHEMA_NAME(schema_id) AS SYNONYM_SCHEMA,
sy.name AS SYNONYM_NAME,
COALESCE (PARSENAME (base_object_name,
3),
DB_NAME (DB_ID ())) AS TABLE_CATALOG,
PARSENAME (base_object_name,
1) AS TABLE_NAME,
COALESCE (PARSENAME (base_object_name,
2),
SCHEMA_NAME (SCHEMA_ID ())) AS TABLE_SCHEMA,
create_date,
modify_date,
is_published,
base_object_name,
object_id AS SYN_OBJECT_ID,
OBJECT_ID(base_object_name) AS BASE_OBJECT_ID,
CONVERT(varchar(max),
sep.value) AS REMARKS
FROM
sys.synonyms sy
LEFT JOIN
sys.extended_properties sep
ON
sy.object_id = sep.major_id
AND sep.minor_id = 0
WHERE
CONCAT(DB_NAME(),
'.',
SCHEMA_NAME(schema_id))
NOT IN ('''')
AND SCHEMA_NAME(schema_id) NOT IN ( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');
Synonym Columns¶
Ensure the query has columns labeled as TABLE_NAME
, SCHEMA
, COLUMN_NAME
, MAX_LENGTH
, PRECISION, SCALE
, TYPE_NAME
, DATA_TYPE
, ORDINAL_POSITION
, and REMARKS
in the SELECT
statement. Fully qualified name for synonym base table will be required.
DECLARE @TEMPPERMISSIONTABLE AS TABLE(dbname VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
owner VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
DboOnly Int,
ReadOnly Int,
SingleUser Int,
Detached Int,
Suspect Int,
Offline Int,
InLoad Int,
EmergencyMode Int,
StandBy Int,
ShutDwn Int,
InRecovery Int,
NotRecovered Int);
INSERT
INTO
@TEMPPERMISSIONTABLE EXECUTE sp_MShasdbaccess;
DECLARE @TABLECOLUMNSSQL NVARCHAR(MAX)
SET
@TABLECOLUMNSSQL = N'';
SELECT
@TABLECOLUMNSSQL = @TABLECOLUMNSSQL + N' UNION ALL
SELECT
''' + QUOTENAME(name) + N''' collate DATABASE_DEFAULT as CATALOG,
t.NAME collate DATABASE_DEFAULT as TABLE_NAME,
t.object_id as table_object_id,
SCHEMA_NAME(t.schema_id) collate DATABASE_DEFAULT as TABLE_SCHEM,
c.name collate DATABASE_DEFAULT as COLUMN_NAME,
c.max_length as MAX_LENGTH,
c.precision as PRECISION,
c.scale as SCALE,
ty.name collate DATABASE_DEFAULT as TYPE_NAME,
ty.name collate DATABASE_DEFAULT as DATA_TYPE,
c.column_id as ORDINAL_POSITION,
CONVERT(varchar(max), sep.value) collate DATABASE_DEFAULT AS REMARKS,
null as COLUMN_DEF,
CASE
WHEN
c.is_nullable = ''1''
THEN
''true''
ELSE
''false''
END
collate DATABASE_DEFAULT AS IS_NULLABLE
FROM
' + QUOTENAME(name) + '.sys.tables AS t
INNER JOIN
' + QUOTENAME(name) + '.sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
JOIN ' + QUOTENAME(name) + '.sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN
' + QUOTENAME(name) + '.sys.extended_properties sep
on t.object_id = sep.major_id
AND c.column_id = sep.minor_id'
COLLATE DATABASE_DEFAULT
FROM
sys.databases sys_dbs
INNER JOIN
@TEMPPERMISSIONTABLE tmp_2
ON
sys_dbs.name COLLATE DATABASE_DEFAULT = tmp_2.dbname COLLATE DATABASE_DEFAULT;
SET
@TABLECOLUMNSSQL = STUFF(@TABLECOLUMNSSQL,
1,
10,
N'');
DECLARE @VIEWCOLUMNSSQL NVARCHAR(MAX)
SET
@VIEWCOLUMNSSQL = N'';
SELECT
@VIEWCOLUMNSSQL = @VIEWCOLUMNSSQL + N' UNION ALL SELECT ''' + QUOTENAME(name) + N''' collate DATABASE_DEFAULT as TABLE_CAT,
t.NAME collate DATABASE_DEFAULT as TABLE_NAME,
t.object_id as table_object_id,
SCHEMA_NAME(t.schema_id) collate DATABASE_DEFAULT as TABLE_SCHEM,
c.name collate DATABASE_DEFAULT as COLUMN_NAME,
c.max_length as MAX_LENGTH,
c.precision as PRECISION,
c.scale as SCALE,
ty.name collate DATABASE_DEFAULT as TYPE_NAME,
ty.name collate DATABASE_DEFAULT as DATA_TYPE,
c.column_id as ORDINAL_POSITION,
null as REMARKS,
null as COLUMN_DEF,
CASE
WHEN
c.is_nullable = ''1''
THEN
''true''
ELSE
''false''
END
collate DATABASE_DEFAULT AS IS_NULLABLE
FROM
' + QUOTENAME(name) + '.sys.views AS t
INNER JOIN
' + QUOTENAME(name) + '.sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
JOIN ' + QUOTENAME(name) + '.sys.types ty ON c.user_type_id = ty.user_type_id'
COLLATE DATABASE_DEFAULT
FROM
sys.databases sys_dbs
INNER JOIN
@TEMPPERMISSIONTABLE tmp_2
ON
sys_dbs.name COLLATE DATABASE_DEFAULT = tmp_2.dbname COLLATE DATABASE_DEFAULT;
SET
@VIEWCOLUMNSSQL = STUFF(@VIEWCOLUMNSSQL,
1,
10,
N'');
DECLARE @TABLECOLUMNS AS TABLE(TABLE_CAT VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
TABLE_NAME VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
TABLE_OBJECT_ID INT,
TABLE_SCHEM VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
COLUMN_NAME VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
MAX_LENGTH INT,
PRECISION INT,
SCALE INT,
TYPE_NAME VARCHAR(128) COLLATE DATABASE_DEFAULT,
DATA_TYPE VARCHAR(128) COLLATE DATABASE_DEFAULT,
ORDINAL_POSITION INT,
REMARKS VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
COLUMN_DEF VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
IS_NULLABLE VARCHAR(1024) COLLATE DATABASE_DEFAULT)
INSERT
INTO
@TABLECOLUMNS EXEC sp_executesql @TABLECOLUMNSSQL ;
DECLARE @VIEWCOLUMNS AS TABLE(TABLE_CAT VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
TABLE_NAME VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
TABLE_OBJECT_ID INT,
TABLE_SCHEM VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
COLUMN_NAME VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
MAX_LENGTH INT,
PRECISION INT,
SCALE INT,
TYPE_NAME VARCHAR(128) COLLATE DATABASE_DEFAULT,
DATA_TYPE VARCHAR(128) COLLATE DATABASE_DEFAULT,
ORDINAL_POSITION INT,
REMARKS VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
COLUMN_DEF VARCHAR(MAX) COLLATE DATABASE_DEFAULT,
IS_NULLABLE VARCHAR(1024) COLLATE DATABASE_DEFAULT)
INSERT
INTO
@VIEWCOLUMNS EXEC sp_executesql @VIEWCOLUMNSSQL ;
SELECT
syn.name COLLATE DATABASE_DEFAULT AS TABLE_NAME,
schema_name(syn.schema_id) COLLATE DATABASE_DEFAULT AS 'SCHEMA',
base_table.COLUMN_NAME COLLATE DATABASE_DEFAULT AS COLUMN_NAME,
base_table.MAX_LENGTH AS MAX_LENGTH,
base_table.PRECISION AS PRECISION,
base_table.SCALE AS SCALE,
DB_NAME() AS 'CATALOG',
base_table.TYPE_NAME COLLATE DATABASE_DEFAULT AS TYPE_NAME,
base_table.TYPE_NAME COLLATE DATABASE_DEFAULT AS DATA_TYPE,
base_table.ORDINAL_POSITION AS ORDINAL_POSITION,
base_table.REMARKS COLLATE DATABASE_DEFAULT AS REMARKS,
base_table.COLUMN_DEF COLLATE DATABASE_DEFAULT AS COLUMN_DEF,
base_table.IS_NULLABLE AS IS_NULLABLE,
NULL AS COLUMN_DEFAULT
FROM
sys.synonyms syn
INNER JOIN
@TABLECOLUMNS base_table
ON
base_table.TABLE_OBJECT_ID = object_id(syn.base_object_name)
AND PARSENAME(base_table.TABLE_CAT,
1) = ISNULL(PARSENAME(syn.base_object_name,
3),
COALESCE (PARSENAME (syn.base_object_name ,
3),
DB_NAME (DB_ID ())))
WHERE
CONCAT(DB_NAME(),
'.',
schema_name(syn.schema_id))
NOT IN ('''')
AND schema_name(syn.schema_id) NOT IN ( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables
')
UNION
SELECT
syn.name COLLATE DATABASE_DEFAULT AS TABLE_NAME,
schema_name(syn.schema_id) COLLATE DATABASE_DEFAULT AS 'SCHEMA',
base_table.COLUMN_NAME COLLATE DATABASE_DEFAULT AS COLUMN_NAME,
base_table.MAX_LENGTH AS MAX_LENGTH,
base_table.PRECISION AS PRECISION,
base_table.SCALE AS SCALE,
DB_NAME() AS 'CATALOG',
base_table.TYPE_NAME COLLATE DATABASE_DEFAULT AS TYPE_NAME,
base_table.TYPE_NAME COLLATE DATABASE_DEFAULT AS DATA_TYPE,
base_table.ORDINAL_POSITION AS ORDINAL_POSITION,
base_table.REMARKS COLLATE DATABASE_DEFAULT AS REMARKS,
base_table.COLUMN_DEF COLLATE DATABASE_DEFAULT AS COLUMN_DEF,
base_table.IS_NULLABLE AS IS_NULLABLE,
NULL AS COLUMN_DEFAULT
FROM
sys.synonyms syn
INNER JOIN
@VIEWCOLUMNS base_table
ON
base_table.TABLE_OBJECT_ID = object_id(syn.base_object_name)
AND PARSENAME(base_table.TABLE_CAT,
1) = ISNULL(PARSENAME(syn.base_object_name,
3),
COALESCE (PARSENAME (syn.base_object_name ,
3),
DB_NAME (DB_ID ())) )
WHERE
CONCAT(DB_NAME(),
'.',
schema_name(syn.schema_id))
NOT IN ('''')
AND schema_name(syn.schema_id) NOT IN ( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');
Index¶
Ensure the query has columns labelled as CATALOG
, SCHEMA
, TABLE_NAME
, COLUMN_NAME
, INDEX_NAME
, TYPE
, FILTER_CONDITION
, and ORDINAL_POSITION
in the SELECT
statement.
SELECT
DB_NAME() AS 'CATALOG',
SCHEMA_NAME(t.schema_id) AS 'SCHEMA',
t.name AS TABLE_NAME,
COL_NAME(ic.object_id,
ic.column_id) AS COLUMN_NAME,
i.name AS INDEX_NAME,
i.type_desc AS TYPE,
i.filter_definition AS FILTER_CONDITION,
NULL AS ASC_OR_DESC,
ic.key_ordinal AS ORDINAL_POSITION
FROM
sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON
i.object_id = ic.object_id
AND i.index_id = ic.index_id
LEFT OUTER JOIN sys.objects t ON
t.object_id = i.object_id
WHERE
CONCAT(DB_NAME(),
'.',
SCHEMA_NAME(t.schema_id))
NOT IN ('''')
AND SCHEMA_NAME (t.schema_id) NOT IN ( 'sys' , 'guest' , 'INFORMATION_SCHEMA' , 'db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter
db_accessadmin' , 'db_backupoperator' , 'db_datareader' , 'db_datawriter' , 'db_ddladmin' , 'db_denydatareader
db_ddladmin' , 'db_denydatareader' , 'db_denydatawriter' , 'db_owner' , 'db_securityadmin' , 'mdw' , 'ssisdb' , 'pdw' , 'QTables');