做一个数据库比对小工具，把SQL做一个笔记
--表
SELECT object_id AS id --表ID
,name --表名
FROM sys.tables
--字段
SELECT A.name --字段名
,B.name AS typename --字段类型
,A.max_length AS typelength --长度（nvarchar要除以2 -1等于max）
,C.text AS defaultvalue --默认值
,A.object_id AS PID --表名
,A.is_nullable --是否为空
,A.is_identity --是否值增长
,s.is_primary_key --是否主键（自带聚集索引）
,s.is_unique --是否唯一
FROM sys.columns A
INNER JOIN sys.types B ON A.user_type_id=B.user_type_id
INNER JOIN sys.tables D ON D.object_id=A.object_id
LEFT JOIN sys.syscomments C ON A.default_object_id=C.id
LEFT JOIN sys.objects OB ON OB.parent_object_id=A.object_id AND OB.type=‘PK‘
LEFT JOIN sys.indexes S ON S.name=OB.name
LEFT JOIN sys.index_columns IC ON IC.object_id = A.object_id AND ic.index_id=s.index_id AND ic.column_id=A.column_id
--非主键关联的索引
SELECT A.NAME --索引名
,D.NAME AS COLUMNNAME --关联字段名
,A.object_id AS PID --表名
,A.type_desc --索引类型
,A.is_unique --是否唯一
FROM SYS.INDEXES A
JOIN SYS.INDEX_COLUMNS B ON (A.object_id = B.object_id AND A.index_id = B.index_id)
JOIN SYS.TABLES C ON (A.object_id = C.object_id)
JOIN SYS.COLUMNS D ON (A.object_id = D.object_id AND B.column_id = D.column_id)
WHERE A.is_primary_key=0 --主键在字段出分析，如果需要，可去掉这个条件
--触发器
SELECT A.parent_id AS PID --表ID
,A.name --触发器名称
,B.text --触发器内容
FROM sys.triggers A
INNER JOIN sys.syscomments B ON A.object_id=B.id
--视图
SELECT A.object_id AS ID
,A.name --视图名
,B.text --视图内容
FROM sys.views A
INNER JOIN sys.syscomments B ON A.object_id=B.id
--存储过程
SELECT A.object_id AS ID
,A.name --存储过程名称
,B.text --存储过程内容
FROM sys.procedures A
INNER JOIN sys.syscomments B ON A.object_id=B.id