110 lines
4.4 KiB
Transact-SQL
110 lines
4.4 KiB
Transact-SQL
IF OBJECT_ID('dev.spCompareTableCount') IS NOT NULL BEGIN
|
|
DROP PROCEDURE dev.spCompareTableCount
|
|
END;
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.spCompareTableCount
|
|
@sourceDb nvarchar(255) -- A forrás adatbázis neve, szögletes zárójelek nélkül: KRETA_KLIK_99_XXX
|
|
,@sourceSchema nvarchar(255) -- A forrás schema neve, szögletes zárójelek nélkül: KR_xxx_Schema
|
|
,@targetSchema nvarchar(255) -- A cél schema neve, szögletes zárójelek nélkül: KR_xxx_Schema (a cél adatbázis az, ahol az SP fut)
|
|
,@table nvarchar(255) = NULL -- Ha csak egy táblát szeretnénk lekérdezni, NULL esetén az összes tábla/view összehasonlításra kerül
|
|
AS
|
|
BEGIN
|
|
DECLARE
|
|
@sql nvarchar(max)
|
|
,@source nvarchar(150)
|
|
,@firstParentCol nvarchar(150)
|
|
,@secondParentCol nvarchar(150)
|
|
,@firstRefObj nvarchar(150)
|
|
,@secondRefObj nvarchar(150)
|
|
|
|
SET @source = IIF(@sourceDb IS NULL OR @sourceDb = '', '', '[' + @sourceDb + '].[') + @sourceSchema + ']'
|
|
|
|
DECLARE @TableTable TABLE (
|
|
SchemaName nvarchar(255)
|
|
,TableName nvarchar(255)
|
|
)
|
|
|
|
DECLARE @ResutlTable TABLE (
|
|
TableName nvarchar(255)
|
|
,SourceCount int
|
|
,TargetCount int
|
|
)
|
|
|
|
IF @table IS NOT NULL BEGIN
|
|
INSERT INTO @TableTable (TableName) VALUES (@table)
|
|
END
|
|
ELSE BEGIN
|
|
--DECLARE @targetSchema nvarchar(255) = 'KR_piarista-god_Schema'
|
|
|
|
INSERT INTO @TableTable (SchemaName, TableName)
|
|
SELECT @targetSchema, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = @targetSchema
|
|
|
|
INSERT INTO @TableTable (SchemaName, TableName)
|
|
SELECT 'dbo', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
|
|
WHERE NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_SCHEMA = 'dbo' AND c.TABLE_NAME = t.TABLE_NAME AND c.COLUMN_NAME = 'ID')
|
|
AND t.TABLE_SCHEMA = 'dbo'
|
|
AND t.TABLE_TYPE = 'BASE TABLE'
|
|
AND TABLE_NAME LIKE 'T[_]%[_]%'
|
|
END
|
|
|
|
DECLARE tableCursor CURSOR FOR
|
|
SELECT SchemaName, TableName
|
|
FROM @TableTable
|
|
|
|
DECLARE
|
|
@SchemaName nvarchar(100)
|
|
,@TableName nvarchar(100)
|
|
|
|
OPEN tableCursor
|
|
FETCH NEXT FROM tableCursor INTO @SchemaName, @TableName
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
IF @SchemaName <> 'dbo' BEGIN
|
|
SET @sql = 'SELECT @TableName, (SELECT COUNT(1) FROM ' + @source + '.' + @TableName + '), (SELECT COUNT(1) FROM [' + @SchemaName + '].' + @TableName + ')'
|
|
END
|
|
ELSE BEGIN
|
|
SELECT TOP(1)
|
|
@firstParentCol = pc.[name]-- AS parent_column_name
|
|
,@firstRefObj = OBJECT_NAME(rc.[object_id]) --as referenced_object_name
|
|
FROM sys.foreign_key_columns f
|
|
INNER JOIN sys.columns pc ON pc.[object_id] = f.parent_object_id AND pc.column_id = f.parent_column_id
|
|
INNER JOIN sys.columns rc ON rc.[object_id] = f.referenced_object_id AND rc.column_id = f.referenced_column_id
|
|
WHERE parent_object_id = OBJECT_ID(@tableName)
|
|
ORDER BY f.constraint_object_id ASC
|
|
|
|
SELECT TOP(1)
|
|
@secondParentCol = pc.[name]-- AS parent_column_name
|
|
,@secondRefObj = OBJECT_NAME(rc.[object_id]) --as referenced_object_name
|
|
FROM sys.foreign_key_columns f
|
|
INNER JOIN sys.columns pc ON pc.[object_id] = f.parent_object_id AND pc.column_id = f.parent_column_id
|
|
INNER JOIN sys.columns rc ON rc.[object_id] = f.referenced_object_id AND rc.column_id = f.referenced_column_id
|
|
WHERE parent_object_id = OBJECT_ID(@tableName)
|
|
ORDER BY f.constraint_object_id DESC
|
|
|
|
SET @sql = N'
|
|
SELECT
|
|
@tableName
|
|
,(SELECT COUNT(1) FROM dbo.' + @tableName + ' x WHERE EXISTS (SELECT 1 FROM ' + @source + '.' + @firstRefObj + ' s WHERE x.' + @firstParentCol + ' = s.ID) AND EXISTS (SELECT 1 FROM ' + @source + '.' + @secondRefObj + ' s WHERE x.' + @secondParentCol + ' = s.ID))
|
|
,(SELECT COUNT(1) FROM dbo.' + @tableName + ' x WHERE EXISTS (SELECT 1 FROM [' + @targetSchema + '].' + @firstRefObj + ' s WHERE x.' + @firstParentCol + ' = s.ID) AND EXISTS (SELECT 1 FROM [' + @targetSchema + '].' + @secondRefObj + ' s WHERE x.' + @secondParentCol + ' = s.ID))
|
|
'
|
|
|
|
END
|
|
PRINT @tableName + ' - ' + @sql
|
|
|
|
INSERT INTO @ResutlTable (TableName, SourceCount, TargetCount)
|
|
EXEC sys.sp_executesql @sql, N'@tableName nvarchar(200)', @tableName
|
|
|
|
FETCH NEXT FROM tableCursor INTO @SchemaName, @TableName
|
|
END
|
|
|
|
CLOSE tableCursor
|
|
DEALLOCATE tableCursor
|
|
|
|
SELECT *, SourceCount-TargetCount AS Diff
|
|
FROM @ResutlTable
|
|
ORDER BY ABS(SourceCount-TargetCount) DESC
|
|
END
|
|
|