SQL Server获取结构及生成结构SQL语句

1.显示所有数据库（非系统创建）

SELECT name FROM  master..sysdatabases 
WHERE name NOT IN 
( 'master', 'model', 'msdb', 'tempdb', 'northwind','pubs')

2.显示某个schema下的表

SELECT table_name FROM information_schema.tables 
WHERE table_schema = ? AND table_type = 'base table'

?为schema名
3.获取表信息

sp_spaceused '?1.?2'

?1为表所在schema,?2为表名。

rows为表记录数
data为表数据所占大小
index为表索引所占大小

4.获取视图及其构建语句

SELECT * FROM sys.all_objects a, sys.sql_modules b 
WHERE a.is_ms_shipped= 0 AND 
      a.object_id = b.object_id AND 
			a.[type] IN ( 'V' ) AND 
			a.schema_id = (SELECT schema_id FROM sys.schemas 
	                    WHERE name = ? 
	  )

?为schema名
5.查看存储过程

SELECT * FROM sys.all_objects a, sys.sql_modules b 
WHERE a.is_ms_shipped= 0 AND 
      a.object_id = b.object_id AND
			a.[type] IN ( 'P' ) AND 
			a.schema_id = (
	       SELECT schema_id FROM sys.schemas 
	       WHERE name = ?
	    )

?为schema名
6.查看函数

SELECT * FROM sys.all_objects a, sys.sql_modules b 
WHERE a.is_ms_shipped= 0 AND 
      a.object_id = b.object_id AND
			a.[type] IN ( 'TF' ) AND 
			a.schema_id = (
	       SELECT schema_id FROM sys.schemas 
	       WHERE name = ?
	    )

?为schema名
7.查看触发器

SELECT * FROM sys.all_objects a, sys.sql_modules b 
WHERE a.is_ms_shipped= 0 AND 
      a.object_id = b.object_id AND
			a.[type] IN ( 'TR' ) AND 
			a.schema_id = (
	       SELECT schema_id FROM sys.schemas 
	       WHERE name = ?
	    )

?为schema名
8.查看同义词

SELECT * FROM sysobjects 
WHERE xtype = 'SN' AND 
      sysobjects.uid = (
	       SELECT schema_id FROM sys.schemas 
	       WHERE name = ? 
	  )

?为schema名
9.查看规则

SELECT * FROM sys.all_objects a, sys.sql_modules b 
WHERE a.is_ms_shipped= 0 AND 
      a.object_id = b.object_id AND
			a.[type] IN ( 'R' ) AND 
			a.schema_id = (
	       SELECT schema_id FROM sys.schemas 
	       WHERE name = ?
	    )

?为schema名
10.查看默认值

SELECT * FROM sys.all_objects a, sys.sql_modules b 
WHERE a.is_ms_shipped= 0 AND 
      a.object_id = b.object_id AND
			a.[type] IN ( 'D' ) AND 
			a.schema_id = (
	       SELECT schema_id FROM sys.schemas 
	       WHERE name = ?
	    )

?为schema名
11.获取表的详细信息（表的列名、数据类型、默认值、是否为主键、外键关联关系等）

SELECT 
CASE
  WHEN c.ORDINAL_POSITION = 1              THEN
    c.TABLE_SCHEMA + '.' + c.TABLE_NAME             
  ELSE
    ''        
END AS '表名',
c.COLUMN_NAME AS '列名',    
CASE
  WHEN ((CHARINDEX('char', c.DATA_TYPE) > 0 OR CHARINDEX('binary', c.DATA_TYPE) > 0) AND c.CHARACTER_MAXIMUM_LENGTH <> - 1)
  THEN c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR (4)) + ')'             
  WHEN ((CHARINDEX('CHAR', c.DATA_TYPE) > 0 OR CHARINDEX('binary', c.DATA_TYPE) > 0) AND c.CHARACTER_MAXIMUM_LENGTH = - 1) 
  THEN c.DATA_TYPE + '(max)'             
  WHEN (CHARINDEX('numeric', c.DATA_TYPE) > 0)              
  THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR (4)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR (4)) + ')'             
  ELSE
    c.DATA_TYPE        
END AS '列数据类型',
ISNULL(c.COLUMN_DEFAULT, '') AS '列默认值',  
CASE
  WHEN c.IS_NULLABLE = 'YES' THEN '√'             
  ELSE ''        
END '是否允许为null',      
CASE
  WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN '√'             
  ELSE ''        
END AS '是否是主键',
CASE
  WHEN tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN '√'             
  ELSE ''        
END AS '是否是外键',
ISNULL(fkcu.COLUMN_NAME, '') AS FOREIGN_KEY,
ISNULL(fkcu.TABLE_NAME, '') AS FOREIGN_TABLE FROM [INFORMATION_SCHEMA].[COLUMNS] c        
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA                                                              AND kcu.TABLE_NAME = c.TABLE_NAME                                                              AND kcu.COLUMN_NAME = c.COLUMN_NAME        
LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA                                                              AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME        
LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA                                                              AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME        
LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA                                                              AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME 
WHERE   c.TABLE_NAME = ?
ORDER BY
  c.ORDINAL_POSITION

?为表名