1. Query all database names
SELECT name FROM sysdatabases
2. Query all table names in the current database without specifying a database. Just select a database and execute SQL directly.
-- 'U': all user table names; 'S': all system table names; 'V': all view table names SELECT name FROM sysobjects WHERE xtype='U' OR xtype='S' OR xtype='V'
3. Get the primary key field of the specified table
SELECT name AS 'PK' FROM SysColumns WHERE id=Object_Id('Table') AND colid=(SELECT TOP 1 keyno FROM sysindexkeys WHERE id=Object_Id('Table'))
4. Query all field names in the specified table
SELECT name FROM SysColumns WHERE id=Object_Id('Table_2')
5. Query all field names and field types in the specified table
SELECT sc.name,st.name FROM SysColumns sc,systypes st WHERE sc.xtype=st.xtype AND sc.id in(SELECT id from sysobjects WHERE xtype='U' AND name='Table')</ pre> <p>6. Obtain part of the data structure of the table</p> <pre> SELECT c.name, c.user_type_id, c.max_length, c.is_nullable, remark = ex.value FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 AND OBJECT_NAME(c.object_id) = 'Table' ORDER BY OBJECT_NAME(c.object_id), c.column_id
7. SqlServer2000 obtains table structure details
SELECT TBL.name AS 'table name',
CONVERT(NVARCHAR(500),DSPTN.value) AS 'Table description',
COL.column_id AS 'field number',
COL.name AS 'field name',
CASE WHEN COL.is_identity = 1 THEN 'YES' ELSE 'NO' END AS 'identity',
CASE WHEN PK.object_id IS NOT NULL AND PK.index_id = COL.column_id THEN 'YES' ELSE 'N0' END AS 'primary key',
TY.name AS 'type',
CAST(COL.max_length AS VARCHAR) AS 'Number of bytes occupied',
CAST(COL.precision AS VARCHAR) AS 'length',
CAST(COL.scale AS VARCHAR) AS 'Decimal places',
CASE WHEN COL.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS 'Allow null',
ISNULL(CONVERT(NVARCHAR(500), DFTCNST.definition), '') AS 'Default value',
CONVERT(NVARCHAR(500),SCOLMS.value) AS 'Field description'
FROM sys.tables TBL
INNER JOIN sys.columns COL ON TBL.object_id = COL.object_id
LEFT JOIN sys.identity_columns IDCOL ON TBL.object_id = IDCOL.object_id AND COL.column_id = IDCOL.column_id
LEFT JOIN sys.types TY ON COL.user_type_id = TY.user_type_id
LEFT JOIN (
SELECT
name,
object_id,
index_id
FROM sys.indexes
WHERE is_primary_key = 1
) PK ON TBL.object_id = PK.object_id
LEFT JOIN sys.default_constraints DFTCNST ON COL.default_object_id = DFTCNST.object_id
LEFT JOIN sys.extended_properties SCOLMS ON COL.object_id = SCOLMS.major_id AND
COL.column_id = SCOLMS.minor_id AND
SCOLMS.name = 'MS_Description'
LEFT JOIN sys.extended_properties DSPTN ON TBL.object_id = DSPTN.major_id AND DSPTN.minor_id = 0
AND DSPTN.name = 'MS_Description'
WHERE TBL.name = 'table'
ORDER BY TBL.name, COL.column_id
8. Obtain table structure details for SqlServer2000 or above
SELECT TBL.name AS 'table name',
CONVERT(NVARCHAR(500),DSPTN.value) AS 'Table description',
COL.column_id AS 'serial number',
COL.name AS 'field name',
CASE WHEN COL.is_identity = 1 THEN 'YES' ELSE 'NO' END AS 'identity',
CASE WHEN PK.object_id IS NOT NULL AND PK.index_id = COL.column_id THEN 'YES' ELSE 'N0' END AS 'primary key',
TY.name AS 'type',
CAST(COL.max_length AS VARCHAR) AS 'Number of bytes occupied',
CAST(COL.precision AS VARCHAR) AS 'length',
CAST(COL.scale AS VARCHAR) AS 'Decimal places',
CASE WHEN COL.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS 'Allow null',
ISNULL(CONVERT(NVARCHAR(500), DFTCNST.definition), '') AS 'Default value',
CONVERT(NVARCHAR(500),SCOLMS.value) AS 'Field description'
FROM sys.tables TBL
INNER JOIN sys.columns COL ON TBL.object_id = COL.object_id
LEFT JOIN sys.identity_columns IDCOL ON TBL.object_id = IDCOL.object_id AND COL.column_id = IDCOL.column_id
LEFT JOIN sys.types TY ON COL.user_type_id = TY.user_type_id
LEFT JOIN (
SELECT
name,
object_id,
index_id
FROM sys.indexes
WHERE is_primary_key = 1
) PK ON TBL.object_id = PK.object_id
LEFT JOIN sys.default_constraints DFTCNST ON COL.default_object_id = DFTCNST.object_id
LEFT JOIN sys.extended_properties SCOLMS ON COL.object_id = SCOLMS.major_id AND
COL.column_id = SCOLMS.minor_id AND
SCOLMS.name = 'MS_Description'
LEFT JOIN sys.extended_properties DSPTN ON TBL.object_id = DSPTN.major_id AND DSPTN.minor_id = 0
AND DSPTN.name = 'MS_Description'
WHERE TBL.name = 'table'
ORDER BY TBL.name, COL.column_id