取 MSSQL SCHEMA信息
获取表名
select TABLE_SCHEMA,TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE';
获取视图名字
select TABLE_SCHEMA,TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='VIEW';
获取表别名
select a.name SCHEMA_NAME,b.name OBJECT_NAME,c.TABLE_CATALOG,c.TABLE_SCHEMA,c.TABLE_NAME,c.TABLE_TYPE
from sys.schemas a, sys.synonyms b,INFORMATION_SCHEMA.TABLES c
where a.schema_id=b.schema_id
and '['+c.TABLE_CATALOG+'].['+c.TABLE_SCHEMA+'].['+c.TABLE_NAME+']' =b.base_object_name
and c.TABLE_TYPE='BASE TABLE';
获取视图别名
select a.name SCHEMA_NAME,b.name OBJECT_NAME,c.TABLE_CATALOG,c.TABLE_SCHEMA,c.TABLE_NAME,c.TABLE_TYPE
from sys.schemas a, sys.synonyms b,INFORMATION_SCHEMA.TABLES c
where a.schema_id=b.schema_id
and '['+c.TABLE_CATALOG+'].['+c.TABLE_SCHEMA+'].['+c.TABLE_NAME+']' =b.base_object_name
and c.TABLE_TYPE='VIEW';
获取字段信息
select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='DBO'
and TABLE_NAME='PDMFILE'
order by ORDINAL_POSITION;
获取外键与主键信息
select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,b.CONSTRAINT_TYPE
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
where a.CONSTRAINT_SCHEMA= b.CONSTRAINT_SCHEMA
and a.CONSTRAINT_NAME= b.CONSTRAINT_NAME
'FOREIGN KEY' 'PRIMARY KEY'
获取存储过程
select SPECIFIC_SCHEMA,SPECIFIC_NAME,ROUTINE_TYPE
from INFORMATION_SCHEMA.ROUTINES
'FUNCTION' 'PROCEDURE'
获取参数列表
select SPECIFIC_SCHEMA,SPECIFIC_NAME,PARAMETER_NAME,DATA_TYPE,PARAMETER_MODE
from INFORMATION_SCHEMA.PARAMETERS
where LEN(PARAMETER_NAME)>0
order by ORDINAL_POSITION;
![](http://www.cnblogs.com/images/cnblogs_com/nanshouyong326/90398/o_20080126(020).jpg)