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 |
sql | SQL Server > sqlsrv | T-SQL >