MSSQL-逻辑级常用命令

--SQL Server 查询表的记录数   
 
--one: 使用系统表.
SELECT object_name (i.id) TableName,
       rows as RowCnt
FROM sysindexes i
INNER JOIN sysObjects o
    ON (o.id = i.id AND o.xType = 'U ')
WHERE indid < 2
ORDER BY rows desc

------查询所有表记录数 

select object_name (i.id) TableName,rows as RowCnt from sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = 'U') WHERE indid < 2 ORDER BY rows desc

--------查询某个表表结构

SELECT CASE
         WHEN col.colorder = 1 THEN
          obj.name
         ELSE
          ''
       END AS 表名,
       col.colorder AS 序号,
       col.name AS 列名,
       t.name AS 数据类型,
       col.length AS 长度,
       ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数,
       CASE
         WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN
          '√'
         ELSE
          ''
       END AS 标识,
       CASE
         WHEN EXISTS (SELECT 1
                 FROM dbo.sysindexes si
                INNER JOIN dbo.sysindexkeys sik
                   ON si.id = sik.id
                  AND si.indid = sik.indid
                INNER JOIN dbo.syscolumns sc
                   ON sc.id = sik.id
                  AND sc.colid = sik.colid
                INNER JOIN dbo.sysobjects so
                   ON so.name = si.name
                  AND so.xtype = 'PK'
                WHERE sc.id = col.id
                  AND sc.colid = col.colid) THEN
          '√'
         ELSE
          ''
       END AS 主键, --'' 外健,
       CASE
         WHEN col.isnullable = 1 THEN
          '√'
         ELSE
          ''
       END AS 允许空,
       ISNULL(comm.text, '') AS 默认值,
       ISNULL(ep. [value], '') AS 说明
  FROM dbo.syscolumns col
  LEFT JOIN dbo.systypes t
    ON col.xtype = t.xusertype
 inner JOIN dbo.sysobjects obj
    ON col.id = obj.id
   AND obj.xtype = 'U'
   AND obj.status >= 0
  LEFT JOIN dbo.syscomments comm
    ON col.cdefault = comm.id
  LEFT JOIN sys.extended_properties ep
    ON col.id = ep.major_id
   AND col.colid = ep.minor_id
   AND ep.name = 'MS_Description'
  LEFT JOIN sys.extended_properties epTwo
    ON obj.id = epTwo.major_id
   AND epTwo.minor_id = 0
   AND epTwo.name = 'MS_Description'
 WHERE obj.name = 'WORK_STATION_INFO' --你要查的表名  
 ORDER BY col.colorder;
————————————————
版权声明：本文为CSDN博主「woxingwoshu_1111」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/woxingwoshu_1111/article/details/129319497