菜鳥工程師

前提是table, sp,...等有一特定的命名規則,否則容易出現系統提供的雜質...

其他常用的語法往後有用到在上來補充...

-- Table
select * from sys.all_objects
WHERE [name] like 'TableName_XXXXX%'
order by modify_date,[name]

-- SP
SELECT         name, object_id, principal_id, schema_id, parent_object_id, type, type_desc,
                          create_date, modify_date, is_ms_shipped, is_published, is_schema_published,
                           is_auto_executed, is_execution_replicated, is_repl_serializable_only,
                          skips_repl_constraints
FROM             DBName.sys.procedures AS procedures_1
WHERE         (SUBSTRING(name, 1, 3) NOT IN ('sp_'))

--查詢TABLE不同的欄位
SELECT         TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
                          COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,
                          CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
                          COLLATION_NAME
FROM             DBName_A.INFORMATION_SCHEMA.COLUMNS AS COLUMNS_1
WHERE         (TABLE_NAME IN ('TableName_XXXXX'))
EXCEPT
select
TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
                          COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,
                          CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
                          COLLATION_NAME
 from DBName_B.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN( 'TableName_XXXXXXX')