Sql-Server

獲取所有數據庫、表和列名以及列定義

  • June 7, 2018

我需要檢查 SQL Server 之間的數據是否相同。

為此，我需要以下詳細資訊。讓我知道這是否可能。

  • 所有數據庫名稱
  • 表名稱
  • 列名稱
  • 表中的列最大長度
╔══════════╦══════════╦══════════════╦══════╗
║ Database ║  Table   ║    column    ║ Size ║
╠══════════╬══════════╬══════════════╬══════╣
║ IBM      ║ Employee ║ EmployeeId   ║    5 ║
║ IBM      ║ Employee ║ EmployeeName ║   49 ║
║ IBM      ║ Project  ║ ProjectID    ║    4 ║
║ IBM      ║ Project  ║ ProjectName  ║  110 ║
║ TCS      ║ Employee ║ EmployeeId   ║    6 ║
║ TCS      ║ Employee ║ EmployeeName ║   51 ║
║ TCS      ║ Project  ║ ProjectID    ║    5 ║
║ TCS      ║ Project  ║ ProjectName  ║  226 ║
╚══════════╩══════════╩══════════════╩══════╝

我有一個我曾經使用過的腳本。這是比較 2 個數據庫，在我比較的範例bocss2中tablebackups。

現在我只使用評論中建議的 Visual Studio 模式比較工具。

它甚至沒有正確格式化，它不是一個完整的、準備好的、沒有錯誤的解決方案，但它至少會給你一些想法。

我還是建議你使用Visual Studio 社區版

--sp_CompareDb @SourceDB='Bocss2',@TargetDb='Tablebackups'


----DROP PROCEDURE sp_CompareDb

--USE MASTER
--GO

--CREATE PROC sp_CompareDb(  
--                            @SourceDB SYSNAME, 
--                            @TargetDb SYSNAME 
--                            )                        
--AS 


/* 
DECLARE @SourceDB SYSNAME='DB1',  
                 @TargetDb SYSNAME='DB2' 
*/ 




DECLARE @SourceDB SYSNAME='Bocss2',  
       @TargetDb SYSNAME='Tablebackups' 

 SET nocount ON  
 SET ansi_warnings ON  
 SET ansi_nulls ON  

 DECLARE @sqlStr VARCHAR(8000)   
 SET @SourceDB= Rtrim(Ltrim(@SourceDB)) 
 IF DB_ID(@SourceDB) IS NULL  
 BEGIN 
       PRINT 'Error: Unable to find the database '+ @SourceDB +'!!!' 
       RETURN 
 END 

 SET @TargetDb= Rtrim(Ltrim(@TargetDb)) 
 IF DB_ID(@TargetDb) IS NULL  
 BEGIN 
       PRINT 'Error: Unable to find the database '+ @TargetDb +'!!!' 
       RETURN 
   END 

 PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25)  
 PRINT 'Comparing databases ' + @SourceDB + ' and ' + @TargetDb  
 PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25)      
 -----------------------------------------------------------------------------------------  
   -- Create temp tables needed to hold the db structure  
   -----------------------------------------------------------------------------------------      

   IF OBJECT_ID('TEMPDB..#TABLIST_SOURCE')IS NOT NULL  
       DROP TABLE #TABLIST_SOURCE 
   IF OBJECT_ID('TEMPDB..#TABLIST_TARGET')IS NOT NULL  
       DROP TABLE #TABLIST_TARGET 
   IF OBJECT_ID('TEMPDB..#IDXLIST_SOURCE')IS NOT NULL  
       DROP TABLE #IDXLIST_SOURCE 
   IF OBJECT_ID('TEMPDB..#IDXLIST_TARGET')IS NOT NULL  
       DROP TABLE #IDXLIST_TARGET 
   IF OBJECT_ID('TEMPDB..#FKLIST_SOURCE')IS NOT NULL  
       DROP TABLE #FKLIST_SOURCE 
   IF OBJECT_ID('TEMPDB..#FKLIST_TARGET')IS NOT NULL  
       DROP TABLE #FKLIST_TARGET 
   IF OBJECT_ID('TEMPDB..#TAB_RESULTS')IS NOT NULL  
       DROP TABLE #TAB_RESULTS 
   IF OBJECT_ID('TEMPDB..#IDX_RESULTS')IS NOT NULL  
       DROP TABLE #IDX_RESULTS 
   IF OBJECT_ID('TEMPDB..#FK_RESULTS')IS NOT NULL  
       DROP TABLE #FK_RESULTS 

 CREATE TABLE #TABLIST_SOURCE 
 (  
       ID INT IDENTITY(1,1), 
       DATABASENAME sysname, 
       TABLENAME SYSNAME , 
       COLUMNNAME SYSNAME, 
       DATATYPE SYSNAME, 
       NULLABLE VARCHAR(15) 
 )  

 CREATE TABLE #TABLIST_TARGET 
 (  
       ID INT IDENTITY(1,1), 
       DATABASENAME sysname, 
       TABLENAME SYSNAME , 
       COLUMNNAME SYSNAME, 
       DATATYPE SYSNAME, 
       NULLABLE VARCHAR(15) 
 )  

 CREATE TABLE #IDXLIST_SOURCE 
 (  
       ID INT IDENTITY(1,1), 
       DATABASENAME sysname, 
       TABLE_NAME SYSNAME, 
       IDX_NAME    SYSNAME , 
       IDX_TYPE VARCHAR(20), 
       IS_PRIMARY_KEY VARCHAR(10), 
       IS_UNIQUE VARCHAR(10), 
       IDX_COLUMNS VARCHAR(1000), 
       IDX_INCLUDED_COLUMNS VARCHAR(1000) 
 ); 

 CREATE TABLE #IDXLIST_TARGET 
 (  
       ID INT IDENTITY(1,1), 
       DATABASENAME sysname, 
       TABLE_NAME SYSNAME, 
       IDX_NAME    SYSNAME , 
       IDX_TYPE VARCHAR(20), 
       IS_PRIMARY_KEY VARCHAR(10), 
       IS_UNIQUE VARCHAR(10), 
       IDX_COLUMNS VARCHAR(1000), 
       IDX_INCLUDED_COLUMNS VARCHAR(1000) 
 ); 

 CREATE TABLE #FKLIST_SOURCE 
 (  
       ID INT IDENTITY(1,1), 
       DATABASENAME sysname, 
       FK_NAME SYSNAME, 
       FK_TABLE sysname, 
       FK_COLUMNS varchar(1000), 
       PK_TABLE sysname, 
       PK_COLUMNS varchar(1000) 
 ); 

 CREATE TABLE #FKLIST_TARGET 
 ( 
       ID INT IDENTITY(1,1), 
       DATABASENAME sysname, 
       FK_NAME SYSNAME, 
       FK_TABLE sysname, 
       FK_COLUMNS varchar(1000), 
       PK_TABLE sysname, 
       PK_COLUMNS varchar(1000) 
 ); 

 CREATE TABLE #TAB_RESULTS 
 ( 
       ID INT IDENTITY(1,1), 
       DATABASENAME sysname, 
       TABLENAME SYSNAME , 
       COLUMNNAME SYSNAME, 
       DATATYPE SYSNAME, 
       NULLABLE VARCHAR(15), 
       REASON VArchar(150) 
 ); 

 CREATE TABLE #IDX_RESULTS 
 ( 
       ID INT IDENTITY(1,1), 
       DATABASENAME sysname, 
       TABLE_NAME SYSNAME, 
       IDX_NAME    SYSNAME , 
       IDX_TYPE VARCHAR(20), 
       IS_PRIMARY_KEY VARCHAR(10), 
       IS_UNIQUE VARCHAR(10), 
       IDX_COLUMNS VARCHAR(1000), 
       IDX_INCLUDED_COLUMNS VARCHAR(1000), 
       REASON Varchar(150) 
 ); 

 CREATE TABLE #FK_RESULTS 
 ( 
       ID INT IDENTITY(1,1), 
       DATABASENAME sysname, 
       FK_NAME SYSNAME, 
       FK_TABLE sysname, 
       FK_COLUMNS varchar(1000), 
       PK_TABLE sysname, 
       PK_COLUMNS varchar(1000), 
       REASON VArchar(150) 
 ); 

 PRINT 'Getting table and column list!'; 
 PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25); 

 BEGIN  
       INSERT INTO #TABLIST_SOURCE(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE) 
       EXEC('SELECT '''+@SourceDB +''', T.TABLE_NAME TABLENAME,  
            C.COLUMN_NAME COLUMNNAME, 
            TY.name + case when TY.name IN (''char'',''varchar'',''nvarchar'') THEN     
               ''(''+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ''max''END+'')'' 
               ELSE     
                   '''' 
               END 
               DATATYPE, 
               CASE WHEN C.is_nullable=''NO'' THEN     
                   ''NOT NULL''  
                   ELSE 
                   ''NULL'' 
               END NULLABLE 
                   FROM '+@SourceDB+'.INFORMATION_SCHEMA.TABLES T  
                       INNER JOIN  '+@SourceDB+'.INFORMATION_SCHEMA.COLUMNS C 
                           ON T.TABLE_NAME=C.TABLE_NAME 
                           and T.TABLE_CATALOG=C.TABLE_CATALOG 
                           and T.TABLE_SCHEMA=C.TABLE_SCHEMA 
                        INNER JOIN '+@SourceDB+'.sys.types TY 
                       ON C.DATA_TYPE =TY.name         
                       ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION');         

       INSERT INTO #TABLIST_TARGET(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE) 
       EXEC('SELECT '''+@TargetDB +''', T.TABLE_NAME TABLENAME,  
            C.COLUMN_NAME COLUMNNAME, 
            TY.name + case when TY.name IN (''char'',''varchar'',''nvarchar'') THEN     
               ''(''+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ''max''END+'')'' 
               ELSE     
                   '''' 
               END 
               DATATYPE, 
               CASE WHEN C.is_nullable=''NO'' THEN     
                   ''NOT NULL''  
                   ELSE 
                   ''NULL'' 
               END NULLABLE 
                   FROM '+@TargetDB+'.INFORMATION_SCHEMA.TABLES T  
                       INNER JOIN  '+@TargetDB+'.INFORMATION_SCHEMA.COLUMNS C 
                           ON T.TABLE_NAME=C.TABLE_NAME 
                           and T.TABLE_CATALOG=C.TABLE_CATALOG 
                           and T.TABLE_SCHEMA=C.TABLE_SCHEMA 
                        INNER JOIN '+@TargetDB+'.sys.types TY 
                       ON C.DATA_TYPE =TY.name         
                       ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION'); 


       PRINT 'Getting index list!'; 
       PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25); 

   INSERT INTO #IDXLIST_SOURCE(DATABASENAME, TABLE_NAME,IDX_NAME,IDX_TYPE,IS_PRIMARY_KEY,IS_UNIQUE,IDX_COLUMNS,IDX_INCLUDED_COLUMNS) 
       EXEC ('WITH CTE AS (  
          SELECT      ic.index_id + ic.object_id AS IndexId,t.name AS TableName  
                      ,i.name AS IndexName 
                                            ,case when ic.is_included_column =0 then 
                                                   c.name end AS ColumnName 
                                           ,case when ic.is_included_column =1 then 
                                                   c.name end AS IncludedColumn 
                                                   ,i.type_desc  
                      ,i.is_primary_key,i.is_unique  
          FROM  '+@SourceDB+'.sys.indexes i  
          INNER JOIN '+@SourceDB+'.sys.index_columns ic  
                  ON  i.index_id    =   ic.index_id  
                  AND i.object_id   =   ic.object_id  
          INNER JOIN '+@SourceDB+'.sys.columns c  
                  ON  ic.column_id  =   c.column_id  
                  AND i.object_id   =   c.object_id  
          INNER JOIN '+@SourceDB+'.sys.tables t  
                  ON  i.object_id = t.object_id  
)  
SELECT '''+@SourceDB+''',c.TableName TABLE_NAME,c.IndexName INDEX_NAME,c.type_desc INDEX_TYPE ,c.is_primary_key IS_PRIMARY_KEY,c.is_unique IS_UNIQUE 
      ,STUFF( ( SELECT '',''+ a.ColumnName FROM CTE a WHERE c.IndexId = a.IndexId ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS COLUMNS  
      ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS INCLUDED_COLUMNS 
FROM   CTE c  
GROUP  BY c.IndexId,c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique  
ORDER  BY c.TableName ASC,c.is_primary_key DESC, C.IndexName ' ); 


   INSERT INTO #IDXLIST_TARGET(DATABASENAME,TABLE_NAME,IDX_NAME,IDX_TYPE,IS_PRIMARY_KEY,IS_UNIQUE,IDX_COLUMNS,IDX_INCLUDED_COLUMNS) 
       EXEC ('WITH CTE AS (  
          SELECT      ic.index_id + ic.object_id AS IndexId,t.name AS TableName  
                      ,i.name AS IndexName 
                                            ,case when ic.is_included_column =0 then 
                                                   c.name end AS ColumnName 
                                           ,case when ic.is_included_column =1 then 
                                                   c.name end AS IncludedColumn 
                                                   ,i.type_desc  
                      ,i.is_primary_key,i.is_unique  
          FROM  '+@TargetDB+'.sys.indexes i  
          INNER JOIN '+@TargetDB+'.sys.index_columns ic  
                  ON  i.index_id    =   ic.index_id  
                  AND i.object_id   =   ic.object_id  
          INNER JOIN '+@TargetDB+'.sys.columns c  
                  ON  ic.column_id  =   c.column_id  
                  AND i.object_id   =   c.object_id  
          INNER JOIN '+@TargetDB+'.sys.tables t  
                  ON  i.object_id = t.object_id  
)  
SELECT '''+@TargetDB+''',c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique  
      ,STUFF( ( SELECT '',''+ a.ColumnName FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS Columns  
      ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName FOR XML PATH('''')),1 ,1, '''') AS IncludedColumns  
FROM   CTE c  
GROUP  BY c.IndexId,c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique  
ORDER  BY c.TableName ASC,c.is_primary_key DESC, C.IndexName '); 


PRINT 'Getting foreign key list!'; 
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25); 

INSERT INTO #FKLIST_SOURCE(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS) 
   EXEC ('With CTE 
               AS 
           (select OBJECT_NAME(FK.parent_object_id,db_id('''+@TargetDB+''')) PK_TABLE,      
                       C1.name PK_COLUMN, 
           object_name(FK.referenced_object_id,db_id('''+@TargetDB+'''))FK_TABLE, 
           C2.name FK_COLUMN, 
           FK.name     FK_NAME 
from 
       '+@SourceDB+'.sys.foreign_keys FK 
           inner join  
       '+@SourceDB+'.sys.foreign_key_columns FKC 
           on FK.object_id=FKC.constraint_object_id 
           inner join  
       '+@SourceDB+'.sys.columns C1  
           on FKC.parent_column_id=C1.column_id 
           and FKC.parent_object_id=C1.object_id 
           inner join  
       '+@SourceDB+'.sys.columns C2 
           on FKC.referenced_column_id=C2.column_id 
           and FKC.referenced_object_id=C2.object_id                             
   ) 
SELECT '''+@SourceDB+''',C.FK_NAME, 
            C.FK_TABLE,             STUFF( ( SELECT '',''+ A.FK_COLUMN FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML PATH('''')),1 ,1, '''') AS FK_COLUMNS, 
            C.PK_TABLE,                           
            STUFF( ( SELECT '',''+ A.PK_Column FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML PATH('''')),1 ,1, '''') AS PK_COLUMNS  
FROM CTE C 
group by C.FK_NAME, 
            C.FK_TABLE,              
            C.PK_TABLE

order by C.FK_NAME, 
            C.FK_TABLE,              
            C.PK_TABLE     

            ')              

INSERT INTO #FKLIST_TARGET(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS) 
EXEC(' 
       With CTE 
AS 
(select OBJECT_NAME(FK.parent_object_id,db_id('''+@TargetDB+''')) PK_TABLE,      
           C1.name PK_COLUMN, 
           object_name(FK.referenced_object_id,db_id('''+@TargetDB+'''))FK_TABLE, 
           C2.name FK_COLUMN, 
           FK.name     FK_NAME 
from 
       '+@TargetDB+'.sys.foreign_keys FK 
           inner join  
       '+@TargetDB+'.sys.foreign_key_columns FKC 
           on FK.object_id=FKC.constraint_object_id 
           inner join  
       '+@TargetDB+'.sys.columns C1  
           on FKC.parent_column_id=C1.column_id 
           and FKC.parent_object_id=C1.object_id 
           inner join  
       '+@TargetDB+'.sys.columns C2 
           on FKC.referenced_column_id=C2.column_id 
           and FKC.referenced_object_id=C2.object_id                             
   ) 
SELECT '''+@TargetDB+''',C.FK_NAME, 
            C.FK_TABLE,             STUFF( ( SELECT '',''+ A.FK_COLUMN FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML PATH('''')),1 ,1, '''') AS FK_COLUMNS, 
            C.PK_TABLE,                           
            STUFF( ( SELECT '',''+ A.PK_Column FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML PATH('''')),1 ,1, '''') AS PK_COLUMNS  
FROM CTE C 
group by C.FK_NAME, 
            C.FK_TABLE,              
            C.PK_TABLE

order by C.FK_NAME, 
            C.FK_TABLE,              
            C.PK_TABLE     

            ')   
END; 

PRINT 'Print column mismatches!'; 
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25); 

INSERT INTO #TAB_RESULTS(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON) 
SELECT @SourceDB AS DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON FROM  
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE 
EXCEPT  
SELECT TS.TABLENAME,TS.COLUMNNAME,TS.DATATYPE,TS.NULLABLE FROM #TABLIST_SOURCE TS 
INNER JOIN 
#TABLIST_TARGET TT 
ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME) TAB_NONMATCH 
CROSS JOIN (SELECT 'Missing Column' As Reason)Tab2  
UNION ALL 
SELECT @TargetDb as DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON FROM  
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET 
EXCEPT  
SELECT TT.TABLENAME,TT.COLUMNNAME,TT.DATATYPE,TT.NULLABLE FROM #TABLIST_TARGET TT 
INNER JOIN 
#TABLIST_SOURCE TS 
ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME) TAB_MATCH  
CROSS JOIN (SELECT 'Missing column ' As Reason)Tab2  

--NON MATCHING COLUMNS 
INSERT INTO #TAB_RESULTS(DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON) 
   SELECT  
       @SourceDB as DATABASENAME, 
       TABLENAME, 
       COLUMNNAME, 
       DATATYPE, 
       NULLABLE, 
       REASON 
   FROM 
   (SELECT * FROM  
   (SELECT TS.TABLENAME,TS.COLUMNNAME,TS.DATATYPE,TS.NULLABLE FROM #TABLIST_SOURCE TS 
       INNER JOIN 
        #TABLIST_TARGET TT ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME)T 
        EXCEPT  
        (SELECT  TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE 
   INTERSECT 
   SELECT  TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET))TT1 
   CROSS JOIN (SELECT 'Definition not matching'AS REASON) t 

   UNION ALL 

   SELECT @TargetDb as DATABASENAME, 
       TABLENAME, 
       COLUMNNAME, 
       DATATYPE, 
       NULLABLE, 
       REASON 
   FROM( 
   SELECT * FROM  
   (SELECT TT.TABLENAME,TT.COLUMNNAME,TT.DATATYPE,TT.NULLABLE FROM #TABLIST_TARGET TT 
       INNER JOIN 
        #TABLIST_SOURCE TS ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME)T 
        EXCEPT 
        (SELECT  TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET 
   INTERSECT 
   SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE))TAB_NONMATCH 
   CROSS JOIN (SELECT 'Definition not matching' AS REASON)T; 

PRINT 'Print index mismatches!'; 
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25); 


INSERT INTO #IDX_RESULTS(DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON) 
SELECT @SourceDB AS DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON FROM  
(SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE 
EXCEPT  
SELECT TS.TABLE_NAME,TS.IDX_NAME, TS.IDX_COLUMNS,TS.IDX_INCLUDED_COLUMNS,TS.IS_PRIMARY_KEY,TS.IS_UNIQUE FROM #IDXLIST_SOURCE TS 
INNER JOIN 
#IDXLIST_TARGET TT 
ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME) TAB_NONMATCH 
CROSS JOIN (SELECT 'Missing Index n' As Reason)Tab2  
UNION ALL 
SELECT @TargetDb as DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON FROM  
(SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET 
EXCEPT  
SELECT TT.TABLE_NAME,TT.IDX_NAME,TT.IDX_COLUMNS,TT.IDX_INCLUDED_COLUMNS,TT.IS_PRIMARY_KEY,TT.IS_UNIQUE FROM #IDXLIST_TARGET TT 
INNER JOIN 
#IDXLIST_SOURCE TS 
ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME) TAB_MATCH  
CROSS JOIN (SELECT 'Missing index ' As Reason)Tab2  

--NON MATCHING INDEX 
INSERT INTO #IDX_RESULTS(DATABASENAME,TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON) 
   SELECT  
       @SourceDB as DATABASENAME, 
       TABLE_NAME, 
       IDX_NAME, 
       IDX_COLUMNS, 
       IDX_INCLUDED_COLUMNS, 
       IS_PRIMARY_KEY, 
       IS_UNIQUE, 
       REASON 
   FROM 
   (SELECT * FROM  
   (SELECT TS.TABLE_NAME, 
       TS.IDX_NAME, 
       TS.IDX_COLUMNS, 
       TS.IDX_INCLUDED_COLUMNS, 
       TS.IS_PRIMARY_KEY, 
       TS.IS_UNIQUE  
       FROM #IDXLIST_SOURCE TS 
       INNER JOIN 
        #IDXLIST_TARGET TT ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME)T 
        EXCEPT  
        (SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE 
   INTERSECT 
   SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET))TT1 
   CROSS JOIN (SELECT 'Definition not matching' AS REASON) t 

   UNION ALL 

   SELECT @TargetDb as DATABASENAME, 
       TABLE_NAME, 
       IDX_NAME, 
       IDX_COLUMNS, 
       IDX_INCLUDED_COLUMNS, 
       IS_PRIMARY_KEY, 
       IS_UNIQUE, 
       REASON 
   FROM( 
   SELECT * FROM  
   (SELECT TT.TABLE_NAME, 
       TT.IDX_NAME, 
       TT.IDX_COLUMNS, 
       TT.IDX_INCLUDED_COLUMNS, 
       TT.IS_PRIMARY_KEY, 
       TT.IS_UNIQUE  FROM #IDXLIST_TARGET TT 
       INNER JOIN 
        #IDXLIST_SOURCE TS ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME)T 
        EXCEPT 
        (SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET 
   INTERSECT 
   SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE))TAB_NONMATCH 
   CROSS JOIN (SELECT 'Definition not matching' AS REASON)T; 


PRINT 'Print key mismatches!'; 
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25); 

INSERT INTO #FK_RESULTS(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON) 
SELECT @SourceDB AS DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON 
FROM  
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE 
EXCEPT  
SELECT TS.FK_NAME,TS.FK_TABLE,TS.FK_COLUMNS,TS.PK_TABLE,TS.PK_COLUMNS FROM #FKLIST_SOURCE TS 
INNER JOIN 
#FKLIST_TARGET TT 
ON TS.FK_NAME=TT.FK_NAME) TAB_NONMATCH 
CROSS JOIN (SELECT 'Missing Index n' As Reason)Tab2  

UNION ALL 

SELECT @TargetDb as DATABASENAME,FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON FROM  
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET 
EXCEPT  
SELECT TT.FK_NAME,TT.FK_TABLE,TT.FK_COLUMNS,TT.PK_TABLE,TT.PK_COLUMNS FROM #FKLIST_TARGET TT 
INNER JOIN 
#FKLIST_SOURCE TS 
ON TS.FK_NAME=TT.FK_NAME) TAB_MATCH  
CROSS JOIN (SELECT 'Missing key' As Reason)Tab2  


--NON MATCHING Keys 
INSERT INTO #FK_RESULTS(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON) 
   SELECT  
       @SourceDB as DATABASENAME, 
       FK_NAME, 
       FK_TABLE, 
       FK_COLUMNS, 
       PK_TABLE, 
       PK_COLUMNS, 
       REASON 
   FROM 
   (SELECT * FROM  
   (SELECT TS.FK_NAME, 
       TS.FK_TABLE, 
       TS.FK_COLUMNS, 
       TS.PK_TABLE, 
       TS.PK_COLUMNS 
       FROM #FKLIST_SOURCE TS 
       INNER JOIN 
        #FKLIST_TARGET TT ON TS.FK_NAME=TT.FK_NAME)T 
        EXCEPT  
        (SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE 
   INTERSECT 
   SELECT  FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET))TT1 
   CROSS JOIN (SELECT 'Definition not matching' AS REASON) t 

   UNION ALL 

   SELECT @TargetDb as DATABASENAME, 
       FK_NAME, 
       FK_TABLE, 
       FK_COLUMNS, 
       PK_TABLE, 
       PK_COLUMNS, 
       REASON 
   FROM( 
   SELECT * FROM  
   (SELECT TT.FK_NAME, 
       TT.FK_TABLE, 
       TT.FK_COLUMNS, 
       TT.PK_TABLE, 
       TT.PK_COLUMNS  FROM #FKLIST_TARGET TT 
       INNER JOIN 
        #FKLIST_SOURCE TS ON TS.FK_NAME=TT.FK_NAME)T 
        EXCEPT 
        (SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET 
   INTERSECT 
   SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE))TAB_NONMATCH 
   CROSS JOIN (SELECT 'Definition not matching' AS REASON)T; 

--Print Final Results     

--SELECT * FROM #TAB_RESULTS 
--where tablename= 'tblBOrder'

SELECT * FROM #IDX_RESULTS 
where TABLE_NAME = 'tblBorder'

--SELECT * FROM #FK_RESULTS 

--select * from #FKLIST_TARGET 


select * FROM #IDXLIST_SOURCE
go
select * FROM #IDXLIST_target
go

你可以試試這個查詢。

CREATE TABLE #tmp(
[dbname] [nvarchar](128) NULL,
[table_schema] [nvarchar](128) NULL,
[column_name] [sysname] NULL,
[data_type] [nvarchar](128) NULL,
[character_maximum_length] [int] NULL,
[is_nullable] [varchar](3) NULL
) 

insert into #tmp
exec sp_msforeachdb 'use ? select db_name(),table_schema,column_name,data_type,character_maximum_length,is_nullable from information_schema.columns'

select * from #temp

drop table #tmp

謝謝

引用自：https://dba.stackexchange.com/questions/150242

相關問答

Sql-Server

sys.objects 列噸是pe_噸是p和Type奇怪的價值’ST'

  • October 27, 2020
檢視問答
Sql-Server

如何在 MS_Description 擴展屬性中格式化文件？

  • March 10, 2020
檢視問答
Sql-Server

如何在 Sql Server 中編寫統計腳本？（使用 T-SQL）

  • November 16, 2016
檢視問答
Sql-Server

是否存在一種方法來確定包含多個文件的文件組中的分配單元的確切文件？

  • March 12, 2016
檢視問答
Sql-Server

添加一世磷_rimary到yy__Column一世s磷r一世米一種r和到和和C○一世你米nIs Primary Key Column到這個查詢

  • February 5, 2016
檢視問答
Sql-Server

使用 sys.dm_os_buffer_descriptors.read_microsec 與 sys.dm_io_virtual_file_stats 計算延遲有什麼區別？

  • September 15, 2022
檢視問答