MSSQL中表结构查询实战技巧

1. MSSQL中常用的表结构查询语句

MSSQL是一种关系型数据库，表结构查询是日常工作中很常见的操作。在MSSQL中，常用的表结构查询语句有如下几种：

1.1. 查询所有表名

使用以下语句可以查询当前数据库中的所有表名：

SELECT name FROM sys.tables;

这里的sys.tables是系统表，它记录了当前数据库中的所有表的信息。通过查询它可以得到当前数据库中所有的表名。

1.2. 查询单个表的所有字段名

使用以下语句可以查询指定表的所有字段名：

SELECT name FROM syscolumns WHERE id=OBJECT_ID('表名');

这里的syscolumns也是系统表，它记录了指定表的所有字段的信息。通过查询它可以得到指定表的所有字段名。

需要注意的是，上面的代码中的'表名'应该替换成需要查询的表的实际名称。例如，如下代码就是查询名为orders的表中的所有字段名：

SELECT name FROM syscolumns WHERE id=OBJECT_ID('orders');

1.3. 查询单个表的所有索引名

使用以下语句可以查询指定表的所有索引名：

SELECT name FROM sysindexes WHERE id=OBJECT_ID('表名');

这里的sysindexes也是系统表，它记录了指定表的所有索引的信息。通过查询它可以得到指定表的所有索引名。

2. MSSQL中查询表结构的实战例子

下面举一个实际的例子，使用MSSQL查询某个具体表的结构信息。

假设我们需要查询一个名为customers的表的结构信息，包括所有字段名、字段类型、字段长度等等各种详细信息。我们可以使用以下代码：

SELECT  

   cols.column_id AS [序号],  
   cols.name AS [字段名],  
   CASE  
        WHEN t.name='bigint' THEN 'bigint'  
        WHEN t.name='binary' THEN 'binary('+ CONVERT(varchar(10),cols.max_length)+')'  
        WHEN t.name='bit' THEN 'bit'  
        WHEN t.name='char' THEN 'char('+ CONVERT(varchar(10),cols.max_length)+')'  
        WHEN t.name='date' THEN 'date'  
        WHEN t.name='datetime' THEN 'datetime'  
        WHEN t.name='datetime2' THEN 'datetime2('+ CONVERT(varchar(10),cols.scale)+')'  
        WHEN t.name='decimal' THEN 'decimal('+ CONVERT(varchar(10),cols.precision)+','+CONVERT(varchar(10),cols.scale)+')'  
        WHEN t.name='float' THEN 'float('+ CONVERT(varchar(10),cols.precision)+')'  
        WHEN t.name='image' THEN 'image'  
        WHEN t.name='int' THEN 'int'  
        WHEN t.name='money' THEN 'money'  
        WHEN t.name='nchar' THEN 'nchar('+ CONVERT(varchar(10),cols.max_length)+')'  
        WHEN t.name='ntext' THEN 'ntext'  
        WHEN t.name='numeric' THEN 'numeric('+ CONVERT(varchar(10),cols.precision)+','+CONVERT(varchar(10),cols.scale)+')'  
        WHEN t.name='nvarchar' THEN 'nvarchar('+ CONVERT(varchar(10),cols.max_length)+')'  
        WHEN t.name='real' THEN 'real'  
        WHEN t.name='smalldatetime' THEN 'smalldatetime'  
        WHEN t.name='smallint' THEN 'smallint'  
        WHEN t.name='smallmoney' THEN 'smallmoney'  
        WHEN t.name='text' THEN 'text'  
        WHEN t.name='time' THEN 'time('+ CONVERT(varchar(10),cols.scale)+')'  
        WHEN t.name='timestamp' THEN 'timestamp'  
        WHEN t.name='tinyint' THEN 'tinyint'  
        WHEN t.name='uniqueidentifier' THEN 'uniqueidentifier'  
        WHEN t.name='varbinary' THEN 'varbinary('+ CONVERT(varchar(10),cols.max_length)+')'    
        WHEN t.name='varchar' THEN 'varchar('+ CONVERT(varchar(10),cols.max_length)+')'  
        ELSE 'unkown'  
   END AS [数据类型],  
   cols.max_length AS [数据长度],  
   ISNULL(obj.is_identity, 0) AS [自增],  
   CASE  
        WHEN (SELECT COUNT(*) FROM sysindexes WHERE id=OBJECT_ID('customers') AND name=cols.name+'_PK')>0 THEN '√' ELSE ''  
   END AS [主键],  
   cols.is_nullable AS [允许空],  
   ISNULL(comm.text, '') AS [默认值]  
FROM sys.columns cols  
   INNER JOIN sys.types t ON cols.user_type_id=t.user_type_id  
   LEFT OUTER JOIN sys.default_constraints obj ON obj.parent_object_id=cols.object_id AND obj.parent_column_id=cols.column_id AND obj.type='D'  
   LEFT OUTER JOIN syscomments comm ON cols.default_object_id=comm.id  
WHERE cols.object_id=OBJECT_ID('customers');

这段代码使用了系统表sys.columns、sys.types、sys.default_constraints和syscomments，以及各种内置函数，可以列出指定表的所有列的详细信息。

通过这段代码的示例，我们不仅可以查询指定表的所有字段名，还可以获得其它关键信息，比如某些字段是否允许为空、是否具有自增特性等等，极大地方便了我们对表结构的查询工作。

总结

MSSQL中的表结构查询是非常常见的操作之一，有多种方法可以实现。本文介绍了MSSQL中常用的表结构查询语句，包括查询所有表名、查询单个表的所有字段名和所有索引名。此外，文章还结合一个实际的例子，演示了如何通过使用多个系统表和内置函数来查询某个具体表的结构信息，以及如何获得更多的关键信息。