菜鸟SQL之查询数据库内容

表

SELECT name as TableName FROM SYS.tables

存储过程

SELECT P.name AS SpName,M.definition AS SpText FROM SYS.procedures AS P 
JOIN SYS.sql_modules AS M ON P.object_id =M.object_id

函数

SELECT F.name AS FunName,M.definition AS FunText FROM SYS.all_objects AS F 
JOIN SYS.sql_modules AS M ON F.object_id =M.object_id WHERE F.type IN ('tf','if','fn') 

视图

SELECT V.name AS ViewName,M.definition AS ViewText FROM SYS.all_objects AS V
JOIN SYS.sql_modules AS M ON V.object_id =M.object_id WHERE V.type='v'

触发器

SELECT Tr.name AS TriggerName,M.definition AS TriggerText FROM SYS.triggers AS Tr
JOIN SYS.sql_modules AS M ON Tr.object_id=M.object_id

 表字段类型

SELECT C.name AS ColumnName,Ty.name AS ColumnType FROM SYS.tables AS Tb
JOIN SYS.columns AS C ON Tb.object_id=C.object_id 
JOIN SYS.types AS Ty ON C.system_type_id=Ty.system_type_id WHERE Tb.name='test1'