SELECT CAST
( o.name AS VARCHAR ( 1000 ) ) AS TABLE_NAME,
CAST ( c.name AS VARCHAR ( 100 ) ) AS COLUMN_NAME,
c.column_id AS ORDINAL_POSITION,
c.PRECISION AS DECIMAL_PRECISION,
c.SCALE AS NUMERIC_SCALE,
SCHEMA_NAME( o.schema_id ) AS TABLE_SCHEMA,
c.system_type_id AS SQL_DATA_TYPE,
c.user_type_id ,
CAST ( TYPE_NAME( c.user_type_id ) AS VARCHAR ( 100 ) ) AS DATA_TYPE,
case when c.IS_NULLABLE=0 then 'No' else 'Yes' end as IS_NULLABLE ,
CASE
WHEN ( c.user_type_id>= 40 AND c.user_type_id<= 62 )
OR c.user_type_id= 104
OR c.user_type_id= 127
OR c.user_type_id= 173 THEN
NULL ELSE
CASE
WHEN c.PRECISION = 0 THEN
NULL ELSE c.PRECISION
END
END AS NUMERIC_PRECISION,
CASE
WHEN c.user_type_id= 231
OR c.user_type_id= 239 THEN
c.max_length / 2
WHEN ( c.user_type_id>= 40 AND c.user_type_id<= 62 )
OR c.user_type_id= 104
OR c.user_type_id= 127
OR c.user_type_id= 173 THEN
NULL ELSE c.max_length
END AS CHARACTER_MAXIMUM_LENGTH,
CAST ( d.definition AS VARCHAR ( 1000 ) ) AS COLUMN_DEFAULT,
CAST ( p.value AS VARCHAR ( 1000 ) ) AS COMMENTS
FROM
sys.columns AS c
INNER JOIN sys.tables AS o ON c.object_id = o.object_id
LEFT OUTER JOIN [sys].[default_constraints] AS d ON d.object_id = c.default_object_id
LEFT OUTER JOIN [sys].[extended_properties] AS p ON c.object_id = p.major_id
AND c.column_id= p.minor_id
AND p.name IN ( 'comment', 'MS_Description' )
WHERE
o.schema_id = SCHEMA_ID( 'DBO' ) UNION ALL
SELECT CAST
( o.name AS VARCHAR ( 1000 ) ) AS TABLE_NAME,
CAST ( c.name AS VARCHAR ( 100 ) ) AS COLUMN_NAME,
c.column_id AS ORDINAL_POSITION,
c.PRECISION AS DECIMAL_PRECISION,
c.SCALE AS NUMERIC_SCALE,
SCHEMA_NAME( o.schema_id ) AS TABLE_SCHEMA,
c.system_type_id AS SQL_DATA_TYPE,
c.user_type_id ,
CAST ( TYPE_NAME( c.user_type_id ) AS VARCHAR ( 100 ) ) AS DATA_TYPE,
case when c.IS_NULLABLE=0 then 'No' else 'Yes' end as IS_NULLABLE ,
CASE
WHEN ( c.user_type_id>= 40 AND c.user_type_id<= 62 )
OR c.user_type_id= 104
OR c.user_type_id= 127
OR c.user_type_id= 173 THEN
NULL ELSE
CASE
WHEN c.PRECISION = 0 THEN
NULL ELSE c.PRECISION
END
END AS NUMERIC_PRECISION,
CASE
WHEN c.user_type_id= 231
OR c.user_type_id= 239 THEN
c.max_length / 2
WHEN ( c.user_type_id>= 40 AND c.user_type_id<= 62 )
OR c.user_type_id= 104
OR c.user_type_id= 127
OR c.user_type_id= 173 THEN
NULL ELSE c.max_length
END AS CHARACTER_MAXIMUM_LENGTH,
CAST ( d.definition AS VARCHAR ( 1000 ) ) AS COLUMN_DEFAULT,
CAST ( p.value AS VARCHAR ( 1000 ) ) AS COMMENTS
FROM
sys.columns AS c
INNER JOIN sys.views AS o ON c.object_id = o.object_id
LEFT OUTER JOIN [sys].[default_constraints] AS d ON d.object_id = c.default_object_id
LEFT OUTER JOIN [sys].[extended_properties] AS p ON c.object_id = p.major_id
AND c.column_id= p.minor_id
AND p.name IN ( 'comment', 'MS_Description' )
WHERE
o.schema_id = SCHEMA_ID( 'DBO' )
sqlserver采集字段的sql语句
最新推荐文章于 2024-04-26 22:50:09 发布