SQL query table structure, process, view, primary key, foreign key, constraint

I. Table Structure Query

Select top (100) percent A. Name as zdm, columnproperty (A. ID, A. Name, 'isidentity ') as BS,
Case when exists (select 1 from DBO. sysindexes Si inner join DBO. sysindexkeys Sik ON Si. ID = Sik. ID
And Si. indid = Sik. indid inner join DBO. syscolumns SC on SC. ID = Sik. ID and SC. colid = Sik. colid
Inner join DBO. sysobjects so on so. Name = So. Name and so. xtype = 'pk' where SC. ID = A. ID and SC. colid = A. colid)
Then '1' else '0' end as ZJ, B. Name as lx, A. length as CD, columnproperty (A. ID, A. Name, 'precision ')
As JD, isnull (columnproperty (A. ID, A. Name, 'Scale'), 0) as xsws, A. isnullable as yxk, isnull (E. Text ,'')
As MRZ, isnull (G. Value, '') as zdsm from DBO. syscolumns as a left Outer Join DBO. policypes as B on A. xtype = B. xusertype
Inner join DBO. sysobjects as D on A. ID = D. id and D. xtype = 'U' and D. Status> = 0 left Outer Join
DBO. syscomments as E on A. cdefault = E. Id left Outer Join SYS. extended_properties as G
On a. ID = G. major_id and A. colid = G. minor_id left Outer Join SYS. extended_properties
As f on D. id = f. major_id and F. minor_id = 0 where D. Name = 'query table name'

 

II,
-- Query stored procedures
Select case. xtype when 'p' then' Stored Procedure 'end as lx,. name, B. text from sysobjects a left Outer Join syscomments B on. id = B. ID where xtype = 'P'
-- Query view
Select case. xtype when 'v'then' view 'end as lx,. name, B. text from sysobjects a left Outer Join syscomments B on. id = B. ID where xtype = 'V'

-- Primary key, foreign key, constraint
Select
Case A. xtype when 'pk' then' primary key 'when 'F' then' foreign key 'when 'C' then' constraint'
End as lx, A. Name as name,
B. Text from sysobjects a left Outer Join syscomments B on A. ID = B. ID
Where (A. xtype in ('C', 'F', 'pk') and
(Objectproperty (A. ID, N 'ismsshipped ') = 0) and A. parent_obj = (select ID from sysobjects where name = 'table _ 2 ')

 

 

The environment is sql2008

The names of the tables and views involved can be found in SQL help.