Jason SQL Server

讀取SQL的SCHEMA

EXEC sp_tables

/*資料表*/
Select * From SysObjects Where xType='U' Order By Name

/*欄位*/
Select * From SysObjects A Inner Join SysColumns B On A.ID=B.ID Where A.xType='U' Order By A.Name,ColID

/*讀取SQL 資料表欄位結構的SQL 語法*/
Select A.Name As TableName,B.ColOrder As ColOrder,B.Name As ColName,C.Name As ColType,B.Length As ColLen,B.XPrec As ColPrecision,B.XScale As ColScale
From (SysObjects A Inner Join SysColumns B On A.ID=B.ID) Inner Join SysTypes C On B.XType=C.XType
Where A.XType='u'
Order By A.Name,B.ColOrder
Jason SQL Server

如何查詢SP內容

[SQL] 取出 MS SQL 預存程式(Stored Procedure) 的文字內容
如何在在所有的存儲過程中找一個關健字

select distinct b.name, b.xtype, object_name(b.parent_obj) as parent_name
from syscomments a, sysobjects b
where a.text like '%查詢文字%' and b.id=a.id and b.status>=0 and
      (b.xtype='P' or b.xtype='TR' or b.xtype='V' or b.xtype='FN')
order by b.xtype, object_name(b.parent_obj), b.name

其中在 SysObject 裡

Name ==> 代表物件的名字
Table, Primary Key, Trigger, Procedure, View, Function name

xtype ==> 代表物件的類別
U = Table, PK = Primary Key, TR = trigger, P = procedure, V = view, FN = function

parent_name:
表示 trigger 或 PKey 的 table name