获取表名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_TYPEfrom sys.schemas a, sys.synonyms b,INFORMATION_SCHEMA.TABLES cwhere 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_TYPEfrom sys.schemas a, sys.synonyms b,INFORMATION_SCHEMA.TABLES cwhere 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.COLUMNSwhere TABLE_SCHEMA='DBO' and TABLE_NAME='PDMFILE'order by ORDINAL_POSITION; 获取外键与主键信息select a.TABLE_SCHEMA,a.TABLE_NAME,a.COLUMN_NAME,b.CONSTRAINT_TYPEfrom INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a, INFORMATION_SCHEMA.TABLE_CONSTRAINTS bwhere 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_MODEfrom INFORMATION_SCHEMA.PARAMETERS where LEN(PARAMETER_NAME)>0order by ORDINAL_POSITION;
转载于:https://www.cnblogs.com/nanshouyong326/archive/2009/07/29/1534080.html
