常用SQL语句

查询数据库中全部表名和行数

SELECT a.NAME
    ,b.rows
FROM sysobjects AS a
INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u')
    AND (
        b.indid IN (
            0
            ,1
            )
        )
ORDER BY a.NAME
    ,b.rows DESC

查询全部数据库信息

select * from sysdatabases

查询当前数据库中所有表名

select * from sysobjects where xtype='U'

参数说明：xtype='U':所有用户表，xtype='S':所有系统表。

查询表Libshp_PonoCid的所有字段、类型、长度

select sc.name as 字段名,st.name as 类型,st.length as 长度 from syscolumns sc join systypes st
on sc.xtype=st.xtype and sc.id in (select id from sysobjects where xtype='U' and name='Libshp_PonoCid')

　查询表的智能主键由那些列组成 

select name as 主键  from syscolumns where id=object_Id('Libshp_PonoCid') 
and colid in (select top 100 keyno from sysindexkeys where id=object_Id('Libshp_PonoCid'))　

 查询一个表与之相关联的存储过程、视图、函数

select a.* from sysobjects a join syscomments b on a.id=b.id and b.text like '%Libshp_PonoCid%'

　查询一个表的全部字段和说明

select a.name as 表名,b.name as 字段名,c.value as 说明
from sys.tables a join sys.columns b on a.object_id=b.object_id
left join sys.extended_properties c on c.major_id=b.object_id and c.minor_id=b.column_id
where a.name='Libshp_PonoCid'　

查询数据库中全部表名，说明 

select id,b.name,value from sys.extended_properties a 
inner join sys.sysobjects b on a.major_id=b.id where a.minor_id=0  and b.xtype='u'  order by name

　查询一个字段属于那些表

select table_name from information_schema.columns
where column_name='名称'