1. Query the current database for all tables
Copy Code code as follows:
SELECT
o.object_id as TableId,
Tablename=o.name,
Tabledesc= O.type
From Sys.columns C
INNER JOIN sys.objects O
On c.[object_id]=o.[object_id]
and o.type= ' U '
and O.is_ms_shipped=0
INNER JOIN sys.types T
On c.user_type_id=t.user_type_id
Left JOIN sys.extended_properties PTB
On Ptb.class=1
and Ptb.minor_id=0
and c.[object_id]=ptb.major_id
WHERE c.column_id=1
ORDER BY TableName
2. Query the current table for all fields, data, constraints
Copy Code code as follows:
Select
Tabname=o.name,
COLUMNLINE=C.COLUMN_ID,
Columnname=c.name,
Typenum=t.name,
Typelength=c.max_length,
Fstate=isnull (G.value,n '),
Isablenull=case when c.is_nullable=1 THEN n ' √ ' ELSE n ' "End,
Defaultdata=isnull (D.definition,n '),
Isidentity=case when C.is_identity=1 THEN n ' √ ' ELSE n ' "End,
Isprimary=case when exists (SELECT 1 from sysobjects where xtype= ' PK ' and parent_obj=c.[object_id] and name in (
Select name from sysindexes where Indid to (select Indid from sysindexkeys where id = c.[object_id) and colid=c.column_id) ) Then ' √ ' Else ' end,
Isforeign=case when exists (SELECT * from Sysforeignkeys FK where C.[object_id]=fk.fkeyid and C.column_id=fk.fkey) Then ' √ ' Else ' End,
Tabforeignname=isnull (IDX. Fkname,n '),
Outnamecol=isnull (Idx.ns,n ')
From Sys.columns C
INNER JOIN sys.objects O
On c.[object_id]=o.[object_id]
and o.type= ' U '
and O.is_ms_shipped=0
INNER JOIN sys.types T
On c.user_type_id=t.user_type_id
Left JOIN sys.extended_properties G
On c.[object_id]=g.major_id and c.column_id=g.minor_id
Left JOIN sys.default_constraints D
On c.[object_id]=d.parent_object_id
and c.column_id=d.parent_column_id
and c.default_object_id=d.[object_id]
Left join Sysforeignkeys FK
On C.[object_id]=fk.fkeyid
and C.column_id=fk.fkey
Left JOIN--index and primary key information
(
SELECT
Idx.fkeyid,
Idx.fkey,
Fkname=o.name,
Ns=ss.name
From Sysforeignkeys IDX
INNER JOIN sys.objects O
On idx.rkeyid=o.[object_id]
and o.type= ' U '
and O.is_ms_shipped=0
Left JOIN Syscolumns SS
On Idx.rkeyid=ss.id
and IDX. Rkey=ss. COLID
) IDX
On C.[object_id]=idx.fkeyid
and C.column_id=idx.fkey
WHERE o.name=n ' {0} '------The name of the table to query
ORDER BY o.name,c.column_id
3. Field
To add single quotes Varchar,char,nvarchar,nchar,text,ntext,datetime
No need to add int,numeric,bit no need to add
Band Length: [Binary],[char],[decimal],[nchar],[numeric],[nvarchar],[varbinary][varchar]
Not with: [bigint],[bit],[ Datetime],[float],[image],[int],[xml],[timestamp],[tinyint],
[uniqueidentifier],[money],[ntext],[real],[ Smalldatetime],[smallint],[smallmoney],
[Sql_variant],[text]
