时间:2021-07-01 10:21:17 帮助过:3人阅读
/*============================================================================== * *Filename:GetTableInfor.sql *Description:获取指定数据库指定表的字段以及字段长度,字段说明等相关数据信息,便于后期 开发代码生成器做准备。tbname可以换成自己需
/*==============================================================================/*============================================================================== * * Filename: GetTableInfor.sql * Description: 获取指定数据库指定表的字段以及字段长度,字段说明等相关数据信息,便于后期 开发代码生成器做准备。tbname可以换成自己需要查询的表名称 即可 * Version: 1.0 * Created: 2012.08.02 * Author: Your name * Company: Copyright (C) Create Family Wealth Power By Peter * ==============================================================================*/ SELECT colorder = C.column_id , ColumnName = C.name , TypeName = T.name , Length = CASE WHEN T.name = 'nchar' THEN C.max_length / 2 WHEN T.name = 'nvarchar' THEN C.max_length / 2 ELSE C.max_length END , Preci = C.precision , Scale = C.scale , IsIdentity = CASE WHEN C.is_identity = 1 THEN N'1' ELSE N'' END , isPK = ISNULL(IDX.PrimaryKey, N'') , Computed = CASE WHEN C.is_computed = 1 THEN N'1' ELSE N'' END , IndexName = ISNULL(IDX.IndexName, N'') , IndexSort = ISNULL(IDX.Sort, N'') , Create_Date = O.Create_Date , Modify_Date = O.Modify_date , cisNull = CASE WHEN C.is_nullable = 1 THEN N'1' ELSE N'' END , defaultVal = ISNULL(D.definition, N'') , deText = ISNULL(PFD.[value], N'') FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id] = O.[object_id] AND ( O.type = 'U' OR O.type = 'V' ) AND O.is_ms_shipped = 0 INNER JOIN sys.types T ON C.user_type_id = T.user_type_id LEFT JOIN sys.default_constraints D ON C.[object_id] = D.parent_object_id AND C.column_id = D.parent_column_id AND C.default_object_id = D.[object_id] LEFT JOIN sys.extended_properties PFD ON PFD.class = 1 AND C.[object_id] = PFD.major_id AND C.column_id = PFD.minor_id LEFT JOIN sys.extended_properties PTB ON PTB.class = 1 AND PTB.minor_id = 0 AND C.[object_id] = PTB.major_id LEFT JOIN -- 索引及主键信息 ( SELECT IDXC.[object_id] , IDXC.column_id , Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id], IDXC.index_id, IDXC.index_column_id, 'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END , PrimaryKey = CASE WHEN IDX.is_primary_key = 1 THEN N'1' ELSE N'' END , IndexName = IDX.Name FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id] = IDXC.[object_id] AND IDX.index_id = IDXC.index_id LEFT JOIN sys.key_constraints KC ON IDX.[object_id] = KC.[parent_object_id] AND IDX.index_id = KC.unique_index_id INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 ( SELECT [object_id] , Column_id , index_id = MIN(index_id) FROM sys.index_columns GROUP BY [object_id] , Column_id ) IDXCUQ ON IDXC.[object_id] = IDXCUQ.[object_id] AND IDXC.Column_id = IDXCUQ.Column_id AND IDXC.index_id = IDXCUQ.index_id ) IDX ON C.[object_id] = IDX.[object_id] AND C.column_id = IDX.column_id WHERE O.name = N'tbname' --数据库中表的名称tbname可以替换成自己需要查询的表 ORDER BY O.name , C.column_id