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