/* 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 on a. id = g. major_id and a. colid = g. minor_id in SQL2005
Left join sysproperties g on a. id = g. id and a. colid = g. smallid
Where d. name = 'table to be query'
Order by a. id, a. colorder
Go