使用SQL语句得到数据库信息
先google了一下,找到了两篇文章.
Schema: How do I show all the primary keys in a database? http://www.aspfaq.com/show.asp?id=2244
Querying System Tables http://www.sqlservercentral.com/columnists/rVasant/queryingsystemtables.asp
下面是用到的一些语句:
–To retrieve all the user created tables:
SELECT name FROM sysobjects WHERE xtype = U
–To get list of all the stored procedures:
SELECT name FROM sysobjects WHERE xtype=P
–To retrieve all the fields of table ‘XYZ’:
SELECT name FROM syscolumns WHERE ID = (SELECT id FROM sysobjects WHERE name=’XYZ’)
下面是一些更高级些的,会用到 INFORMATION_SCHEMA views(sql2000以上),查看sql server的online help会看到更多的view
–得到Customers表上的所有的主键的name, type, 长度, default value
SELECT K.COLUMN_NAME,
C.DATA_TYPE,
C.CHARACTER_MAXIMUM_LENGTH,
C.COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
OIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.COLUMNS C
ON C.Table_Name = T.TABLE_NAME
AND C.COLUMN_NAME = K.COLUMN_NAME
WHERE T.CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND T.TABLE_NAME = ‘Customers’
此外,How do I show all the primary keys in a database一文中的SQL的缩进格式也很值得学习
SELECT
T.TABLE_NAME,
T.CONSTRAINT_NAME,
K.COLUMN_NAME,
K.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE
T.CONSTRAINT_TYPE = ‘PRIMARY KEY’
— AND T.TABLE_NAME = ‘table_name’
ORDER BY
T.TABLE_NAME,
K.ORDINAL_POSITION
