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 c.max_length = -1 AND TYPE_NAME(c.user_type_id) IN ('varchar', 'nvarchar', 'varbinary') THEN CONCAT(TYPE_NAME(c.user_type_id), '(max)')
                    WHEN TYPE_NAME(c.user_type_id) IN ('char', 'varchar', 'binary', 'varbinary') THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
                    WHEN TYPE_NAME(c.user_type_id) IN ('nchar', 'nvarchar') THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length / 2, ')')
                    WHEN TYPE_NAME(c.user_type_id) IN ('decimal', 'numeric') THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.precision, ',', c.scale, ')')
                    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 c.max_length = -1 AND TYPE_NAME(c.user_type_id) IN ('varchar', 'nvarchar', 'varbinary') THEN CONCAT(TYPE_NAME(c.user_type_id), '(max)')
                    WHEN TYPE_NAME(c.user_type_id) IN ('char', 'varchar', 'binary', 'varbinary') THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length, ')')
                    WHEN TYPE_NAME(c.user_type_id) IN ('nchar', 'nvarchar') THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.max_length / 2, ')')
                    WHEN TYPE_NAME(c.user_type_id) IN ('decimal', 'numeric') THEN CONCAT(TYPE_NAME(c.user_type_id), '(', c.precision, ',', c.scale, ')')
                    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');