Sql Server2000(2005)里面获得数据库表名称、列名称等

 

--1：获取当前数据库中的所有用户表

select Name from sysobjects where xtype='u' and status>=0

--2：获取某一个表的所有字段

select name from syscolumns where id=object_id(N'表名')

--3：查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

--4：查看当前数据库中所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P'

--5：查询用户创建的所有数据库

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

或者

select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

--6：查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns
where table_name = N'表名'

--7:获取数据库文件路径

select ltrim(rtrim(filename)) from 数据库名..sysfiles where charindex('MDF',filename)>0
or
select ltrim(rtrim(filename)) from 数据库名..sysfiles where charindex('LDF',filename)>0

 

8-- 获取存储过程内容 
--    Name = Stored Procedure Name. 
SELECT text 
FROM syscomments 
WHERE id = ( SELECT id FROM sysobjects WHERE name = ' 存储过程名称 ') 
--9查看当前库中所有的触发器:
查询脚本：SELECT * FROM Sysobjects WHERE xtype = 'TR'

--查看当前库中所有的触发器和与之相对应的表：
查询脚本：SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id WHERE tb1.type='TR'

--某表索引

sp_helpindex 'WFDATAACCESS'

 

SQL2005

SELECT 表名 = case when a.colorder = 1 then d.name else '' end , 表说明 = case when a.colorder = 1 then isnull (f.value, '' ) else '' end , 字段序号 = a.colorder, 字段名 = a.name, 标识 = case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end , 主键 = case when exists ( SELECT 1 FROM sysobjects where xtype = ' PK ' and parent_obj = a.id and name in ( SELECT name FROM sysindexes WHERE indid in ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid))) then ' √ ' else '' end , 类型 = b.name, 占用字节数 = a.length, 长度 = COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ), 小数位数 = isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ), 允许空 = case when a.isnullable = 1 then ' √ ' else '' end , 默认值 = isnull (e. text , '' ), 字段说明 = isnull (g. [ value ] , '' ) 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 d.name = ' C_PARTY_SPREADER ' -- 如果只查询指定表,加上此条件 order by a.id,a.colorder