- /*==============================================================================
- *
- * Filename: GetTableInfor.sql
- * Description: 获取指定数据库指定表的字段以及字段长度,字段说明等相关数据信息,便于后期
- 开发代码生成器做准备。tbname可以换成自己需要查询的表名称 即可
- *
- ==============================================================================*/
- 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
- --该片段来自于http://www.codesnippet.cn/detail/130820135117.html
来源: http://www.codesnippet.cn/detail/130820135117.html