MSSQLServer use commands to query detailed information of all databases, tables, and table columns

/* 1. query all databases in sqlserver */

Use master

Go

Select * From sysdatabases where dbid> 4/* The where condition is used to remove the system database */

Go

/* 2. query all databases (Stored Procedures) in sqlserver )*/

Exec sp_helpdb

/* 3. query tables in the specified database */

Use Database Name

Go

Select * From sysobjects where xtype = 'U'/* The where condition is used to remove the system table */

Go

/* 4. display details of all tables in the current database */

Use Database Name

Go

Select

Table name = case when
A. colorder = 1 then D. Name else ''end,

FIELD No. = A. colorder,

Field name = A. Name,

Id = case when
Columnproperty (A. ID, A. Name, 'isidentity ') = 1
Then' √ 'else
''End,

Primary Key = case when
Exists

(

Select 1 from sysobjects where
Xtype = 'pk' and name in

(

Select name from sysindexes where
Indid in

(

Select
Indid from sysindexkeys
Where id = A. ID
And colid = A. colid

)

)

)

Then '√'
Else''
End,

Type = B. Name,

Bytes occupied = A. length,

Length = columnproperty (A. ID, A. Name, 'precision '),

Decimal places = isnull (columnproperty (A. ID, A. Name, 'Scale'), 0 ),

Allow null = case when
A. isnullable = 1 then' √ 'else' 'end,

Default Value = isnull (E. Text ,''),

Field description = isnull (G. [value], '')/* when this column is bound to the gridview, if you select Dynamic generation column, it will never be bound. the specified column is normal after being manually specified. The reason is unknown */

From syscolumns

Left join policypes B on A. xtype = B. xusertype

Inner join sysobjects D on A. ID = D. id and D. xtype = 'U' and D. Name <> 'dtproperties'

Left join syscomments e on A. cdefault = E. ID

// Use left join SYS. extended_properties g in sql2005
On a. ID = G. major_id and A. colid = G. minor_id

Left join sysproperties g on A. ID = G. ID and
A. colid = G. smallid

Where D. Name = 'table to be query'

Order
A. ID, A. colorder

Go