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

Core Connector Core connectors are included with all Alation platform tiers (subject to each tier’s connector limits) and are fully supported by 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, ')')
        WHEN TYPE_NAME(c.user_type_id) IN ('time', 'datetime2', 'datetimeoffset') THEN CONCAT(TYPE_NAME(c.user_type_id),'(',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, ')')
        WHEN TYPE_NAME(c.user_type_id) IN ('time', 'datetime2', 'datetimeoffset') THEN CONCAT(TYPE_NAME(c.user_type_id),'(',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');