编程中常用的SQL收藏

选择字号： 超大 标准 发布时间：2009-2-17 19:7:42 | 作者：admin | 0个评论 | 人浏览

1、查询某一表的主键   sp_pkeys   tabelname

2、查询数据库中的所有表的名称

      select [name] from sysobjects where xtype = 'U' and [name] <> 'sysdiagrams'  and category=0

      order by [name]

3、查询指定表的列名，类型，长度

      select syscolumns.name AS  ColumnName,systypes.name  AS TypeName,syscolumns.length  as length
      from syscolumns,sysobjects,systypes  where syscolumns.id=sysobjects.id and  syscolumns.xtype=       systypes.xtype    and  sysobjects.name= '表名'   order by syscolumns.colid

4、查询指定表的详细信息

     select * from sys.all_columns where object_id=(select id from sysobjects where name='表名')

5、查询数据库的所有类型    select * from systypes

6、查询数据库的基本信息 select * from sys.all_objects

7、 查询表名及主键

      select syscolumns.name as Primarkey , sysobjects.NAME AS TableName
      FROM syscolumns,sysobjects where syscolumns.id=sysobjects.id 
      and   sysobjects.xtype='U'and sysobjects.name<>'dtproperties'and
      exists
     (  SELECT 1 FROM sysobjects where xtype='PK' and name in
     (SELECT name FROM sysindexes WHERE indid   
     in(SELECT indid FROM sysindexkeys WHERE id =syscolumns.id AND colid=syscolumns.colid )))

8、查询服务器中的所有数据库名称

     Select Name FROM Master.dbo.SysDatabases orDER BY Name

标签：

猜你喜欢