declare @table_name varchar(100)-- 表名 set @table_name='bqcform101'--============表结构select 类别,表名or字段名,描述,字段类型,是否自增,允许为NULL,默认值 from ( SELECT '表名' 类别,-1 column_id,tbs.name 表名or字段名,ds.value 描述,'' 字段类型,'' 是否自增,'' 允许为NULL,'' 默认值,1 rn FROM sys.extended_properties ds LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id WHERE ds.minor_id=0 and tbs.name=@table_name union SELECT @table_name 类别 ,c.column_id ,C.name 表名or字段名 ,s.value 描述 ,字段类型 = T.name + CASE T.user_type_id WHEN 41 THEN '('+CAST(C.scale AS VARCHAR) +')'-- time WHEN 42 THEN '('+CAST(C.scale AS VARCHAR) +')' -- datetime2 WHEN 43 THEN '('+CAST(C.scale AS VARCHAR) +')' -- datetimeoffset WHEN 106 THEN '('+CAST(C.precision AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')' -- decimal WHEN 108 THEN '('+CAST(C.precision AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')' -- numeric WHEN 165 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- varbinary WHEN 167 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- varchar WHEN 173 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- binary WHEN 175 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- char WHEN 231 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1)/2 AS VARCHAR),'MAX') +')'-- nvarchar(该字段校检根据实际情况) WHEN 239 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- nchar ELSE '' END ,case when C.is_identity=1 then '是' else '' end 是否自增--cast(C.is_identity as varchar(10)) 是否自增 ,case when C.is_nullable=1 then '是'else '' end 允许为NULL ,默认值 = ISNULL(STUFF(LEFT(D.definition,LEN(D.definition)-1),1,1,''),'') ,3 rn FROM sys.columns C INNER JOIN sys.types T ON C.user_type_id = T.user_type_id LEFT JOIN sys.default_constraints D ON D.[object_id] =C.default_object_id AND D.parent_object_id = C.[object_id] AND D.parent_column_id = C.column_id left join (select major_id,minor_id,value from sys.extended_properties) s on s.major_id = c.object_id and s.minor_id = c.column_id WHERE C.[object_id] = OBJECT_ID(@table_name) ) s order by column_id,rn --============主键select Primary_COLUMN_NAME = convert(sysname,c.name) from sysindexes i, syscolumns c, sysobjects o where o.id = object_id(@table_name) and o.id = c.id and o.id = i.id and (i.status & 0x800) = 0x800 and (c.name = index_col (@table_name, i.indid, 1) or c.name = index_col (@table_name, i.indid, 2) or c.name = index_col (@table_name, i.indid, 3) or c.name = index_col (@table_name, i.indid, 4) or c.name = index_col (@table_name, i.indid, 5) or c.name = index_col (@table_name, i.indid, 6) or c.name = index_col (@table_name, i.indid, 7) or c.name = index_col (@table_name, i.indid, 8) or c.name = index_col (@table_name, i.indid, 9) or c.name = index_col (@table_name, i.indid, 10) or c.name = index_col (@table_name, i.indid, 11) or c.name = index_col (@table_name, i.indid, 12) or c.name = index_col (@table_name, i.indid, 13) or c.name = index_col (@table_name, i.indid, 14) or c.name = index_col (@table_name, i.indid, 15) or c.name = index_col (@table_name, i.indid, 16) )
