Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to obtain the notes, primary keys and other information of the fields in the MSSQL table structure". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. The MSSQL2000 copy code is as follows: SELECT table name = case when a.colorder=1 then d.name else''end, table description = case when a.colorder=1 then isnull (f. Valuegrammar') else''end, field serial number = a.colorder, field name = a.name, identity = case when COLUMNPROPERTY (a.idmema.name' IsIdentity') = 1 then '√' else''end. Primary key = case when exists (SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (SELECT name FROM sysindexes WHERE indid in (SELECT indid FROM sysindexkeys WHERE id= a.id AND colid=a.colid) then '√' else''end, type = b.name, number of bytes occupied = a.length, length = COLUMNPROPERTY (a. ID. Allow null = case when a.isnullable=1 then '√' else''end, default value = isnull (e.textstatement'), field description = isnull (g. [value] '') FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtypewritten tables U' and d.nameboards dtproperties` left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id and a.colid=g.smallid left join sysproperties f on d.id=f.id and f.smallid=0 where d.nameplates tables FIQUDET'- if only the specified table is queried, add this condition order by a.idjie a.colorder

2. MSSQL2005 use test-- database go-2005 implements field attribute statistics (the system table sysproperties description table and fields in 2000 do not exist Replace with sys.extended_properties view in 2005) select [table name] = c.Name, [table description] = isnull (f. [value],''), [column name] = a.Name, [column serial number] = a.Column_id, [identification] = case when is_identity=1 then '√' else''end [primary key] = case when exists (select 1 from sys.objects where parent_object_id=a.object_id and type=N'PK' and name in (select Name from sys.indexes where index_id in (select indid from sysindexkeys where and colid=a.column_id) then '√' else''end, [Type] = b.Name [number of bytes] = case when a.[ max _ length] =-1 and b.namemedicomentxml' then 'max/2G' when b.namemedicxml' then' 2 ^ 31-1 bytes / 2G' else rtrim (a. [max _ length]) end, [length] = ColumnProperty (a. [empty] = case when a.is_nullable=1 then '√' else''end, [column description] = isnull (e. [value],'), [default] = isnull (d.text) '') from sys.columns a left join sys.types b on a.user_type_id=b.user_type_id inner join sys.objects c on a.object_id=c.object_id and c. Typewriter U'left join syscomments d on a.default_object_id=d.ID left join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 left join sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 [/ code]

This is the end of the content of "how to get the notes, primary keys and other information of the fields in the MSSQL table structure". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.