sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | List Table Variable Schema

SELECT 
 T.type_table_object_id
 ,T.NAME [TABLE NAME]
 ,SCH.NAME [SCHEMA NAME]
 ,C.COLUMN_ID
 ,C.NAME [COLUMN NAME]
 ,UPPER(TYP.NAME) + ISNULL(CASE 
WHEN TYP.NAME IN ('NVARCHAR', 'NTEXT', 'NCHAR') 
THEN '(' + CASE C.MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CAST(C.MAX_LENGTH / 2 AS NVARCHAR) END + ')'
WHEN TYP.NAME IN ('VARCHAR', 'TEXT', 'CHAR', 'BINARY', 'VARBINARY')
THEN '(' + CASE C.MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CAST(C.MAX_LENGTH AS NVARCHAR) END + ')'
WHEN TYP.NAME IN ('DATETIME2', 'DATETIMEOFFSET', 'TIME')
THEN '(' + CAST(C.SCALE AS NVARCHAR) + ')'
WHEN TYP.NAME IN ('DECIMAL', 'NUMERIC')
THEN '(' + CAST(C.PRECISION AS NVARCHAR) + ', ' + CAST(C.SCALE AS NVARCHAR) + ')'
END, '') TYPE
 ,CASE C.IS_NULLABLE WHEN 1 THEN 'NULLABLE' ELSE 'NOT NULL' END NULLABLE
  ,ISNULL(CASE 
WHEN TYP.NAME IN ('NVARCHAR', 'NTEXT', 'NCHAR') 
THEN 'String'
WHEN TYP.NAME IN ('VARCHAR', 'TEXT', 'CHAR')
THEN 'String'
WHEN TYP.NAME IN ('BINARY', 'VARBINARY')
THEN 'Byte[]'
WHEN TYP.NAME IN ('DATETIME2', 'DATETIMEOFFSET', 'TIME', 'DATETIME')
THEN 'DateTime'
WHEN TYP.NAME IN ('DECIMAL', 'NUMERIC')
THEN 'Double'
WHEN TYP.NAME IN ('SQL_VARIANT')
THEN 'Object'
WHEN TYP.NAME IN ('FSUDT.ACTPRODUCT')
THEN 'System.Numerics.BigInteger'
WHEN TYP.NAME IN ('INT')
THEN 'Int32'
WHEN TYP.NAME IN ('BIGINT')
THEN 'Int64'
WHEN TYP.NAME IN ('BIT')
THEN 'Bool'
WHEN TYP.NAME IN ('SMALLINT')
THEN 'Byte'
END, '') [TYPE.NET]
 
 ,ISNULL(KC.TYPE, '') PK
 ,ISNULL(KC2.NAME, '') UK
 --,CASE 
--WHEN INDCOL.IS_DESCENDING_KEY IS NULL THEN '' 
--WHEN INDCOL.IS_DESCENDING_KEY = 1 THEN '1' 
--ELSE '0' END  [DESC]
 --,CASE ISNULL(FKC.CONSTRAINT_COLUMN_ID, '') WHEN 0 THEN '' ELSE 'FK' END FK
 --,ISNULL(SCHEMA_NAME(REF.SCHEMA_ID), '') FK_REF_TBLSCH
 --,ISNULL(REF.NAME, '') FK_REF_TBL
 --,ISNULL(REFCOL.NAME, '') FK_REF_TBLCOL
 ,CASE C.IS_IDENTITY 
WHEN 1 THEN 'IDENTITY(' + 
CAST(IC.SEED_VALUE AS NVARCHAR) + ', ' + 
CAST(IC.INCREMENT_VALUE AS NVARCHAR) + ')'  
ELSE '' END [IDENTITY]
 ,C.IS_NULLABLE
 
 ,C.MAX_LENGTH
 ,C.PRECISION
 ,C.SCALE
 ,C.USER_TYPE_ID
 ,C.SYSTEM_TYPE_ID
 ,C.IS_COMPUTED
 ,C.default_object_id
 ,ISNULL(object_definition(C.default_object_id), '') [DEFAULT]

--SELECT *
FROM 
 SYS.ALL_COLUMNS C 
INNER JOIN SYS.table_types T
ON C.OBJECT_ID = T.type_table_object_id
INNER JOIN SYS.SCHEMAS SCH
ON T.SCHEMA_ID = SCH.SCHEMA_ID

INNER JOIN SYS.TYPES TYP
ON C.USER_TYPE_ID = TYP.USER_TYPE_ID
LEFT JOIN SYS.IDENTITY_COLUMNS IC
ON IC.OBJECT_ID = C.OBJECT_ID 
AND IC.COLUMN_ID = C.COLUMN_ID
LEFT JOIN SYS.INDEX_COLUMNS INDCOL
ON INDCOL.OBJECT_ID = T.type_table_object_id
AND INDCOL.COLUMN_ID = C.COLUMN_ID
--AND INDCOL.INDEX_ID = 1

LEFT JOIN SYS.INDEX_COLUMNS INDCOL2
ON INDCOL2.OBJECT_ID = T.type_table_object_id
AND C.COLUMN_ID = INDCOL2.COLUMN_ID
LEFT JOIN SYS.KEY_CONSTRAINTS KC 
ON T.type_table_object_id = KC.PARENT_OBJECT_ID
AND INDCOL2.INDEX_ID = KC.UNIQUE_INDEX_ID
AND KC.TYPE = 'PK'
LEFT JOIN SYS.INDEX_COLUMNS INDCOL3
ON INDCOL3.OBJECT_ID = T.type_table_object_id
AND C.COLUMN_ID = INDCOL3.COLUMN_ID
LEFT JOIN SYS.KEY_CONSTRAINTS KC2
ON T.type_table_object_id = KC2.PARENT_OBJECT_ID
AND INDCOL3.INDEX_ID = KC2.UNIQUE_INDEX_ID
AND KC2.TYPE = 'UQ'
--WHERE T.NAME ='SYSTEMCOREACCOUNT'
ORDER BY [TABLE NAME], column_id
Comments