SQL statement Query database table name/column name/primary key/Auto Grow value instance _mssql

SQL statement query table name/column name/primary key/Auto grow value in database

----A table created by a user in a query database

----JSJ01 as database name

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

--field information in the query table

---docs as table name
----SELECT * from syscolumns where id = object_id (' docs ')

----querying all types in the database
----Select Name,xtype from systypes

----Two tables, showing all the fields and corresponding data types in the table

----syscolumns field ' xtype ' corresponds to the ' Xusertype ' in systypes, the ' Name ' field in systypes is the data type of the field
----Docs as table name
Select A.name as fieldname,b.name as type from
Syscolumns as a
Join Systypes as B
On a.xtype = B.xusertype
where id=object_id (' docs ')

----Docs as Datasheet name: Query table field, type, description

Select A.name fieldname,b.name type,c.value comment from
Syscolumns as a
Full Join systypes 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 changes the user As schema
On A.name=c.objname COLLATE chinese_prc_ci_as-----Collation (sometimes not, if the collation of the two tables is different, the error will be)
--join sysproperties C
--on a.id=c.major_id
where id=object_id (' docs ')

----The primary key in the query table, there is no primary key is empty, if it is more than one combination of primary keys there are multiple values PK primary key FK for foreign keys
---jsj01 the foreign key for the database name docs for the table name FK

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

----The fields that are automatically grown in the query table are not empty, if there is only one
----Docs as table name

SELECT a.name Column_name,b.name data_type
From syscolumns A,systypes b
WHERE a.id=object_id (' docs ') and A.xtype = B.xusertype
And a.autoval is not NULL


Author pukuimin1226