COLUMNS

Information Schema Views Code behind (INFORMATION_SCHEMA.COLUMNS)!!!

SELECT  DB_NAME() AS TABLE_CATALOG ,
        SCHEMA_NAME(o.schema_id) AS TABLE_SCHEMA ,
        o.name AS TABLE_NAME ,
        c.name AS COLUMN_NAME ,
        COLUMNPROPERTY(c.object_id, c.name, 'ordinal') AS ORDINAL_POSITION ,
        CONVERT(NVARCHAR(4000), OBJECT_DEFINITION(c.default_object_id)) AS COLUMN_DEFAULT ,
        CONVERT(VARCHAR(3), CASE c.is_nullable
                              WHEN 1 THEN 'YES'
                              ELSE 'NO'
                            END) AS IS_NULLABLE ,
        ISNULL(TYPE_NAME(c.system_type_id), t.name) AS DATA_TYPE ,
        COLUMNPROPERTY(c.object_id, c.name, 'charmaxlen') AS CHARACTER_MAXIMUM_LENGTH ,
        COLUMNPROPERTY(c.object_id, c.name, 'octetmaxlen') AS CHARACTER_OCTET_LENGTH ,
        CONVERT(TINYINT, CASE -- int/decimal/numeric/real/float/money
                              WHEN c.system_type_id IN ( 48, 52, 56, 59,
                                                         60, 62, 106, 108,
                                                         122, 127 )
                              THEN c.precision
                         END) AS NUMERIC_PRECISION ,
        CONVERT(SMALLINT, CASE	-- int/money/decimal/numeric
                               WHEN c.system_type_id IN ( 48, 52, 56, 60,
                                                          106, 108, 122,
                                                          127 ) THEN 10
                               WHEN c.system_type_id IN ( 59, 62 ) THEN 2
                          END) AS NUMERIC_PRECISION_RADIX ,	-- real/float
        CONVERT(INT, CASE	-- datetime/smalldatetime
                          WHEN c.system_type_id IN ( 40, 41, 42, 43, 58,
                                                     61 ) THEN NULL
                          ELSE odbcscale(c.system_type_id, c.scale)
                     END) AS NUMERIC_SCALE ,
        CONVERT(SMALLINT, CASE -- datetime/smalldatetime
                               WHEN c.system_type_id IN ( 40, 41, 42, 43,
                                                          58, 61 )
                               THEN odbcscale(c.system_type_id, c.scale)
                          END) AS DATETIME_PRECISION ,
        CONVERT(SYSNAME, NULL) AS CHARACTER_SET_CATALOG ,
        CONVERT(SYSNAME, NULL) AS CHARACTER_SET_SCHEMA ,
        CONVERT(SYSNAME, CASE WHEN c.system_type_id IN ( 35, 167, 175 )	-- char/varchar/text
                                   THEN COLLATIONPROPERTY(c.collation_name,
                                                          'sqlcharsetname')
                              WHEN c.system_type_id IN ( 99, 231, 239 )	-- nchar/nvarchar/ntext
                                   THEN N'UNICODE'
                         END) AS CHARACTER_SET_NAME ,
        CONVERT(SYSNAME, NULL) AS COLLATION_CATALOG ,
        CONVERT(SYSNAME, NULL) AS COLLATION_SCHEMA ,
        c.collation_name AS COLLATION_NAME ,
        CONVERT(SYSNAME, CASE WHEN c.user_type_id > 256 THEN DB_NAME()
                         END) AS DOMAIN_CATALOG ,
        CONVERT(SYSNAME, CASE WHEN c.user_type_id > 256
                              THEN SCHEMA_NAME(t.schema_id)
                         END) AS DOMAIN_SCHEMA ,
        CONVERT(SYSNAME, CASE WHEN c.user_type_id > 256
                              THEN TYPE_NAME(c.user_type_id)
                         END) AS DOMAIN_NAME
FROM    sys.objects o
        JOIN sys.columns c ON c.object_id = o.object_id
        LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE   o.type IN ( 'U', 'V' )

Advertisements
This entry was posted in Database Administration, Information Schema. Bookmark the permalink.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s