DEFINITIONS

Definitions More Info.
Definition ID908
TitleSQL
CategoryNOTES
DefinitionNot null kosulu olup bos kayit girisi olan tabloyu bulma.
Definition Description-- Asagidaki ornek replikasyona katilan tablolarin NOT NULL kosulu olan alanlarini ele arak, eger bir sekilde bos kayit girilmisse sorunlu tabloyu bulmak icin yazilmistir.


DECLARE @tableName NVARCHAR(100)
,@sql VARCHAR(MAX)
,@operationValues NVARCHAR(10)

-- Replile tablolarının listesi alttaki tabloda
DECLARE ReplicaTbls CURSOR FOR
SELECT TableName
FROM [dbr].[DBA_CaptureInstance]
WHERE IsCDCEnabled = 1

OPEN ReplicaTbls;
-- Perform the first fetch.
FETCH NEXT FROM ReplicaTbls INTO @tableName;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Primarykey NVARCHAR(MAX) = NULL
,@Primarykey2 NVARCHAR(MAX) = NULL
,@RowGuid NVARCHAR(50) = NULL
,@filteredColumn NVARCHAR(20)=NULL
,@filteredColumnVal NVARCHAR(20)=NULL
,@NotNullColumns VARCHAR(MAX) = NULL
,@NotNullColumnsISNULLCondition VARCHAR(MAX) = NULL
,@NotNullColumnsSPACECondition VARCHAR(MAX) = NULL

-- SELECT kısmı için
SELECT @NotNullColumns = COALESCE(@NotNullColumns +',','') +CHAR(13)+CHAR(10)+CAST((LTRIM(RTRIM(column_name))) AS VARCHAR(8000))
FROM INFORMATION_SCHEMA.COLUMNS AS NNC
WHERE NNC.table_name = @tableName
AND NNC.IS_NULLABLE='NO'
ORDER BY NNC.ORDINAL_POSITION ASC;

--WHERE KOŞUL KISMI İÇİN
SELECT @NotNullColumnsISNULLCondition = COALESCE(@NotNullColumnsISNULLCondition++' IS NULL OR','') +CHAR(13)+CHAR(10)+CAST((LTRIM(RTRIM(column_name))) AS VARCHAR(8000))
FROM INFORMATION_SCHEMA.COLUMNS AS NNC
WHERE NNC.table_name = @tableName
AND NNC.IS_NULLABLE='NO'
ORDER BY NNC.ORDINAL_POSITION ASC;

--SELECT @NotNullColumnsSPACECondition = COALESCE(@NotNullColumnsSPACECondition +' ='''' OR ','') +CHAR(13)+CHAR(10)+CAST((LTRIM(RTRIM(column_name))) AS VARCHAR(8000))
-- FROM INFORMATION_SCHEMA.COLUMNS AS NNC
-- WHERE NNC.table_name = @tableName
-- AND NNC.IS_NULLABLE='NO'
--ORDER BY NNC.ORDINAL_POSITION ASC;

/* Eğer where kısmına boş kayıtları da filtrelemesini istiyorsak,
o zaman aşağıdaki sorguyu dinamik sql içine ekle

SELECT '+@NotNullColumns+'
FROM '+@tableName+'
WHERE ('+@NotNullColumnsISNULLCondition+' IS NULL)
OR ('+@NotNullColumnsSPACECondition+' = '''')
*/

PRINT ('--'+@tableName)
SET @sql = 'SELECT '+@NotNullColumns+'
FROM '+@tableName+' WITH (NOLOCK)
WHERE ('+@NotNullColumnsISNULLCondition+' IS NULL) '

PRINT (@sql)
FETCH NEXT FROM ReplicaTbls INTO @tableName ;

END
CLOSE ReplicaTbls;
DEALLOCATE ReplicaTbls;
RecordBycunay
Record Date24-12-2018 11:33:11
Düzenle
Kopyala
Sil