SQL statement queries the table name/column name/primary key/auto-increment instance in the database

SQL statement query the table name/column name/primary key/automatic Growth Value in the database

---- Query tables created by users in the database

---- Jsj01 indicates the database name.

Select name tablename from jsj01.. sysobjects where type = 'U' and name not in ('dtproperties ')

-- Query the field information in the table

--- Docs: Table Name
---- Select * from syscolumns where id = object_id ('docs ')

---- Query all types in the database
---- Select name, xtype from policypes

---- Two-table join query: display all fields and corresponding data types in the table

---- The field 'xtype' in syscolumns corresponds to 'xusertype' in policypes, And the 'name' field in policypes is the data type of the field.
---- Docs indicates the table name.
Select a. name as fieldname, B. name as type from
Syscolumns as
Join policypes as B
On a. xtype = B. xusertype
Where id = object_id ('docs ')

---- Docs is the data table name: queries table fields, types, and descriptions.

Select a. name fieldname, B. name type, c. value comment from
Syscolumns as
Full join policypes as B
On a. xtype = B. xusertype
Full join: fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'docs', 'column', default) as c ---- This is version 2000, 2005 change user to schema
On a. name = c. objname COLLATE Chinese_PRC_CI_AS ----- sorting rule)
-- Join sysproperties c
-- On a. id = c. major_id
Where id = object_id ('docs ')

---- The primary key in the query table. No primary key is blank. If multiple composite primary keys exist, multiple values pk is the primary key. fk is the foreign key.
--- Jsj01 is the database name. docs is the table name. fk represents the foreign key.

Select column_name as primarykey, * from
[Jsj01]. INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Where Table_name = 'docs' and constraint_name like 'fk _ %'

-- Select * from sysobjects WHERE OBJECT_NAME (sysobjects. parent_obj) = 'docs' -- and xtype = 'pk'
-- Select * from sysconstraints where id = object_id ('docs ')
-- Select * from syscolumns where id = object_id ('docs ')
-- Select * from sysindexes
-- Select * from sysindexkeys

---- Query the Automatically increasing field in the table. It is not empty. If there is only one field
---- Docs indicates the table name.

SELECT a. name column_name, B. name data_type
FROM syscolumns a, policypes B
WHERE a. id = object_id ('docs') and a. xtype = B. xusertype
AND a. autoval is not null


Author: pukuimin1226