INFORMATION_SCHEMA.ROUTINE_COLUMNS

This query shows the Code behind  of INFORMATION_SCHEMA.ROUTINE_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
	,c.column_id AS ORDINAL_POSITION
	,CONVERT(NVARCHAR(4000), OBJECT_DEFINITION(c.default_object_id)) AS COLUMN_DEFAULT
	,CONVERT(VARCHAR(3), CASE 
			WHEN c.is_nullable = 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 (
		'TF'
		,'IF'
		,'FT'
		)
Advertisements

INFORMATION_SCHEMA.COLUMN_PRIVILEGES

The next script is the Code behind of the INFORMATION_SCHEMA.COLUMN_PRIVILEGES view.

SELECT USER_NAME(p.grantor_principal_id) AS GRANTOR
	,USER_NAME(p.grantee_principal_id) AS GRANTEE
	,DB_NAME() AS TABLE_CATALOG
	,SCHEMA_NAME(o.schema_id) AS TABLE_SCHEMA
	,o.NAME AS TABLE_NAME
	,c.NAME AS COLUMN_NAME
	,CONVERT(VARCHAR(10), CASE p.type
			WHEN 'SL'
				THEN 'SELECT'
			WHEN 'UP'
				THEN 'UPDATE'
			WHEN 'RF'
				THEN 'REFERENCES'
			END) AS PRIVILEGE_TYPE
	,CONVERT(VARCHAR(3), CASE p.STATE
			WHEN 'G'
				THEN 'NO'
			WHEN 'W'
				THEN 'YES'
			END) AS IS_GRANTABLE
FROM sys.database_permissions p
	,sys.objects o
	,sys.columns c
WHERE o.type IN (
		'U'
		,'V'
		)
	AND o.object_id = c.object_id
	AND p.class = 1
	AND p.major_id = o.object_id
	AND p.minor_id = c.column_id
	AND p.type IN (
		'RF'
		,'SL'
		,'UP'
		)
	AND p.STATE IN (
		'G'
		,'W'
		)
	AND (
		p.grantee_principal_id = 0
		OR p.grantee_principal_id = DATABASE_PRINCIPAL_ID()
		OR p.grantor_principal_id = DATABASE_PRINCIPAL_ID()
		)

COLUMN_DOMAIN_USAGE

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


SELECT  DB_NAME() AS DOMAIN_CATALOG ,
        SCHEMA_NAME(t.schema_id) AS DOMAIN_SCHEMA ,
        t.name AS DOMAIN_NAME ,
        DB_NAME() AS TABLE_CATALOG ,
        SCHEMA_NAME(o.schema_id) AS TABLE_SCHEMA ,
        o.name AS TABLE_NAME ,
        c.name AS COLUMN_NAME
FROM    sys.objects o
        JOIN sys.columns c ON c.object_id = o.object_id
        JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE   c.user_type_id > 256

REFERENTIAL_CONSTRAINTS

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


SELECT  DB_NAME() AS CONSTRAINT_CATALOG ,
        SCHEMA_NAME(f.schema_id) AS CONSTRAINT_SCHEMA ,
        f.name AS CONSTRAINT_NAME ,
        DB_NAME() AS UNIQUE_CONSTRAINT_CATALOG ,
        SCHEMA_NAME(t.schema_id) AS UNIQUE_CONSTRAINT_SCHEMA ,
        i.name AS UNIQUE_CONSTRAINT_NAME ,
        CONVERT(VARCHAR(7), 'SIMPLE') AS MATCH_OPTION ,
        CONVERT(VARCHAR(11), CASE f.update_referential_action
                               WHEN 0 THEN 'NO ACTION'
                               WHEN 1 THEN 'CASCADE'
                               WHEN 2 THEN 'SET NULL'
                               WHEN 3 THEN 'SET DEFAULT'
                             END) AS UPDATE_RULE ,
        CONVERT(VARCHAR(11), CASE f.delete_referential_action
                               WHEN 0 THEN 'NO ACTION'
                               WHEN 1 THEN 'CASCADE'
                               WHEN 2 THEN 'SET NULL'
                               WHEN 3 THEN 'SET DEFAULT'
                             END) AS DELETE_RULE
FROM    sys.foreign_keys f
        LEFT JOIN sys.indexes i ON i.object_id = f.referenced_object_id
                                   AND i.index_id = f.key_index_id
        LEFT JOIN sys.tables t ON t.object_id = f.referenced_object_id


CHECK_CONSTRAINTS

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

SELECT  DB_NAME() AS CONSTRAINT_CATALOG ,
        SCHEMA_NAME(schema_id) AS CONSTRAINT_SCHEMA ,
        name AS CONSTRAINT_NAME ,
        CONVERT(NVARCHAR(4000), definition) AS CHECK_CLAUSE
FROM    sys.check_constraints

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' )

Parameter-driven ASC/DESC Sort ( CASE in ORDER BY )

This post is came up from a MSDN forum post that discussed last night . Problem was to write CASE in ORDER BY cluase without using dynamic sql. This is my solution that I just want to share it.


DECLARE @SortDesc BIT = 1;
SELECT  *
FROM    Sales.SalesOrderHeader
ORDER BY
CASE WHEN @SortDesc = 1 THEN SalesOrderID END DESC ,
CASE WHEN ISNULL(@SortDesc, 0) <> 1 THEN SalesOrderID END ASC ;

TABLES

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

    SELECT  DB_NAME() AS TABLE_CATALOG ,
            s.name AS TABLE_SCHEMA ,
            o.name AS TABLE_NAME ,
            CASE o.type
              WHEN 'U' THEN 'BASE TABLE'
              WHEN 'V' THEN 'VIEW'
            END AS TABLE_TYPE
    FROM    sys.objects o
            LEFT JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE   o.type IN ( 'U', 'V' )