SQL Server 获取表的基本信息、字段列表、存储过程内容及参数列表

select name from master.dbo.sysdatabases order by name;-- 获取数据库列表

select * from sysobjects where type = 'p' and category = 0 order by name;-- 获取数据库存储过程列表 

select text from syscomments where id = ( select id from sysobjects where name = 'uspLogError');--获取指定存储过程的文本内容 

select text from syscomments where id in ( select id from sysobjects  where name in (select name from sysobjects where type = 'p' and category = 0)); --获取所有存储过程的文本内容

select  tab.name as 表名,  --获取sql server表名
        sch.name as 表所有者,  
        tab.create_date as 创建日期,  
        tab.modify_date as 修改日期  
from   sys.tables as tab  
       join sys.schemas as sch on tab.schema_id = sch.schema_id  
where  1 = 1 and tab.name = 'product';  --表名不区分大小写sql2005测试

select col.name as 列名, --获取sql server表的列名与其属性 
       tps.name as 类型,  
       col.precision as 精度,   
       col.max_length as 最大长度,  
       col.is_nullable as 是否允许为空,     
       col.is_identity as 标识,   
       pro.value as 说明注释 
from   sys.tables as tab  
       join sys.columns as col on tab.object_id = col.object_id  
       join sys.types as tps on col.system_type_id = tps.system_type_id  
                  and is_user_defined = 0 and tps.name <> 'sysname'  
       left join sys.extended_properties as pro on pro.major_id = tab.object_id  
                  and pro.minor_id = col.column_id and pro.name = 'ms_description'   
where   tab.name ='product' order by col.column_id; --表名不区分大小写sql2005测试 

select sc.name as 参数名 ,  --获取sql server指定存储过程参数列表
       st.name as 类型 ,  
       sc.length as 长度
from   syscolumns sc  
       join sysobjects so on so.id = sc.id  
       join systypes st on sc.xtype = st.xtype  
where  so.name = 'uspgetmanageremployees';