常用SQL语句

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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

查询全部数据库信息

?

1	select * from sysdatabases

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

?

1	select * from sysobjects where xtype='U'

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

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

?

1 2	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')

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

?

1 2	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'))

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

?

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

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

?

1 2 3 4

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'

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

?

1 2	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

　查询一个字段属于那些表

?

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