SongKer 发布时间:2014-07-22 分类:Web 阅读:6753次 添加评论
--查询数据库里面的所有表名集合
SELECT name FROM sysobjects WHERE xtype='U' AND name <> 'dtproperties' order by name asc
--查询数据库里面的所有表名集合及描述
SELECT TableName=case when a.colorder=1 then d.name else '' end, Desctription=case when a.colorder=1 then (isnull(f.value,d.name)) else '' end, TableNameDesctription = d.name +'---'+ CONVERT(varchar, case when a.colorder=1 then (isnull(f.value,'')) else '' end,1) FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 where a.colorder=1 and d.name<>'sysdiagrams' --where d.name='orders' --如果只查询指定表,加上此条件 order by d.Name
--查询所有视图列表
SELECT name FROM sysobjects WHERE xtype='V' AND name <> 'dtproperties' order by name asc
--得到所函数列表
SELECT name FROM sysobjects WHERE xtype='FN' AND name <> 'dtproperties' order by name asc
--得到所有存储过程列表
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name asc
--得到所有数据库
SELECT name FROM sys.databases order by name asc
/// select name from sysobjects where xtype='u'
/// C = CHECK 约束
/// D = 默认值或 DEFAULT 约束
/// F = FOREIGN KEY 约束
/// L = 日志
/// FN = 标量函数
/// IF = 内嵌表函数
/// P = 存储过程
/// PK = PRIMARY KEY 约束(类型是 K)
/// RF = 复制筛选存储过程
/// S = 系统表
/// TF = 表函数
/// TR = 触发器
/// U = 用户表
/// UQ = UNIQUE 约束(类型是 K)
/// V = 视图
/// X = 扩展存储过程
///
发表评论:
◎欢迎您的参与讨论。