SQL Server gets all tables and table fields

SELECT [Number]=a.colorder, [column] =a.name, [Datatype]=b.name, [ Length]=columnproperty (a.id,a.name, ' PRECISION '), [identity]=case when ColumnProperty (A.id,a.name, ' isidentity ') =1 Then ' √ ' else ' end, [key]=case if exists (SELECT 1 from sysobjects where xtype= ' PK ' and pare                             Nt_obj=a.id and name in (the SELECT name from the sysindexes WHERE indid in (                              SELECT indid from Sysindexkeys WHERE id = a.id and colid=a.colid)) and then ' √ ' Else ' end, [Isnullable]=case when a.isnullable=1 then ' √ ' else ' end, [Defau Lt]=isnull (E.text, "), [Remark]=isnull (G.[value], ') from Syscolum NS A LEFT join systypes B on a.xusertype=b.xusertype inner join SY Sobjects D on A.id=d.id and d.xType= ' U ' and d.name<> ' dtproperties ' left joins syscomments E on a.cdefault=e.id                              Left joins Sys.extended_properties G on a.id=g.major_id and a.colid=g.minor_id Left join Sys.extended_properties F in d.id=f.major_id and f.minor_id=0 where d.name= ' bpms_organization ' ORDER by A.I D,a.colorder

 SELECT ID = d.id, Field = case when a.colorder = 1                                                                         Then D.name ELSE ' END, R                                                                              Emark = A.colorder = 1 then ISNULL (F.value, ")                                                                    ELSE ' END,                                                                    ParentID = 0,                                                                    Colorder = 0 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 J OIN sys.extended_properties F on d.id = f.major_id WHERE a.co Lorder = 1 and f.minor_id = 0

DECLARE @TableInfo TABLE (name VARCHAR (50), [Rows]                                  CHAR (one), reserved varchar, data varchar (50),                                Index_size varchar (unused), varchar (50) ) DECLARE @TableName TABLE (name VARCHAR) DECL                                    is @name VARCHAR () INSERT into @TableName (name    ) SELECT O.name from sysobjects O, sysindexes i WHERE o.id = I.id and O.xtype = ' U ' and I.indID < 2 ORDER by I.rows DESC, O.name  While EXISTS (SELECT 1 from @TableName                                            ) BEGIN SELECT TOP 1 @name = name from @TableName DELETE @T                                            Ablename WHERE [email protected] INSERT into @TableInfo                                              (name, [rows],  Reserved, data, index_size                          , unused)                  EXEC sys.sp_spaceused @name END--table name, record count, use size, index use size, unused amount of space,                                    Table Description SELECT f.*,p.tdescription from @TableInfo F left JOIN (                                                 SELECT name = case-A.colorder = 1 then d.name ELSE ' END, tdescription = case when A .                                                  Colorder = 1 Then ISNULL (F.value, ') ELSE '                                            END 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 sys                                            . extended_properties F on d.id = f.major_id WHERE A.colorder = 1 and f.minor_id = 0) P on F.name=p.name

　　

SELECT  a.name                                                    from    syscolumns a                                                            INNER joins SYSOBJECTS D on a.id = d.id and                                                              d.xtype = ' U ' and                                                              D.nam E <> ' dtproperties '                                                    where   d.name = ' Bpms_button ' and 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)))

　　

　　

SQL Server gets all tables and table fields