取 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;