IF OBJECT_ID('tempdb..#TABLOBILGILERI') IS NOT NULL
    DROP TABLE #TABLOBILGILERI;

CREATE TABLE #TABLOBILGILERI (
    TABLOADI NVARCHAR(max),
	ALANADI  NVARCHAR(max),
    DEGER NVARCHAR(MAX),
    SAYISI INT
);

DECLARE @sql NVARCHAR(MAX) = '';


SELECT @sql = @sql + '
INSERT INTO #TABLOBILGILERI (TABLOADI, DEGER, SAYISI,ALANADI)
SELECT ''' + col.TABLE_NAME + ''' AS TABLOADI, KODU, COUNT(KODU) AS SAYISI,''KODU''
FROM ' + col.TABLE_SCHEMA + '.' + col.TABLE_NAME + '
GROUP BY KODU
HAVING COUNT(KODU) > 1;'
FROM INFORMATION_SCHEMA.COLUMNS col
WHERE col.COLUMN_NAME = 'KODU' AND col.TABLE_NAME NOT IN ('STOK_HAR_OZET','DOVIZ_KURLARI')
  AND EXISTS (
    SELECT 1 
    FROM INFORMATION_SCHEMA.COLUMNS col2
    INNER JOIN INFORMATION_SCHEMA.TABLES tab
    ON col2.TABLE_NAME = tab.TABLE_NAME
    AND col2.TABLE_SCHEMA = tab.TABLE_SCHEMA
    WHERE col2.TABLE_NAME = col.TABLE_NAME 
    AND col2.COLUMN_NAME = 'KODU'
    AND tab.TABLE_TYPE = 'BASE TABLE'
  )
  AND EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
    WHERE tc.TABLE_NAME = col.TABLE_NAME
    AND tc.TABLE_SCHEMA = col.TABLE_SCHEMA
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND ccu.COLUMN_NAME = 'KODU'
  );

EXEC sp_executesql @sql;


SET @sql = '';
SELECT @sql = @sql + '
INSERT INTO #TABLOBILGILERI (TABLOADI, DEGER, SAYISI,ALANADI)
  SELECT ''' + col.TABLE_NAME + ''' AS TABLOADI,
  STUFF((
    SELECT DISTINCT '','' + CAST(FIRMA_KODU AS NVARCHAR(128))
    FROM ' + col.TABLE_SCHEMA + '.' + col.TABLE_NAME + ' 
    FOR XML PATH('''')
  ), 1, 1, '''') AS KODU,
  COUNT( DISTINCT FIRMA_KODU) AS SAYISI,''FIRMA_KODU'' 
  FROM (
    SELECT FIRMA_KODU, COUNT(*) AS TekrarSayisi
    FROM ' + col.TABLE_SCHEMA + '.' + col.TABLE_NAME + '
    GROUP BY FIRMA_KODU
  ) AS TMP
  HAVING COUNT( DISTINCT FIRMA_KODU) > 1;
'
FROM INFORMATION_SCHEMA.COLUMNS col
WHERE col.COLUMN_NAME = 'FIRMA_KODU' AND col.TABLE_NAME NOT IN ('STOK_HAR_OZET','DOVIZ_KURLARI')
  AND EXISTS (
    SELECT 1 
    FROM INFORMATION_SCHEMA.COLUMNS col2
    INNER JOIN INFORMATION_SCHEMA.TABLES tab
    ON col2.TABLE_NAME = tab.TABLE_NAME
    AND col2.TABLE_SCHEMA = tab.TABLE_SCHEMA
    WHERE col2.TABLE_NAME = col.TABLE_NAME 
    AND col2.COLUMN_NAME = 'FIRMA_KODU'
    AND tab.TABLE_TYPE = 'BASE TABLE'
  )
  AND EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
    WHERE tc.TABLE_NAME = col.TABLE_NAME
    AND tc.TABLE_SCHEMA = col.TABLE_SCHEMA
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND ccu.COLUMN_NAME = 'FIRMA_KODU'
  );

EXEC sp_executesql @sql;
 
 
SET @sql = '';
  
SELECT @sql = @sql + '
INSERT INTO #TABLOBILGILERI (TABLOADI, DEGER, SAYISI, ALANADI)
SELECT ''' + col.TABLE_NAME + ''' AS TABLOADI,counter,COUNT(*),''COUNTER''
    FROM ' + col.TABLE_SCHEMA + '.' + col.TABLE_NAME + '
    GROUP BY COUNTER
    HAVING COUNT(*) >1
'
FROM INFORMATION_SCHEMA.COLUMNS col
WHERE col.COLUMN_NAME = 'COUNTER' AND col.TABLE_NAME NOT IN ('STOK_HAR_OZET','DOVIZ_KURLARI')
  AND EXISTS (
    SELECT 1 
    FROM INFORMATION_SCHEMA.COLUMNS col2
    INNER JOIN INFORMATION_SCHEMA.TABLES tab
    ON col2.TABLE_NAME = tab.TABLE_NAME
    AND col2.TABLE_SCHEMA = tab.TABLE_SCHEMA
    WHERE col2.TABLE_NAME = col.TABLE_NAME 
    AND col2.COLUMN_NAME = 'COUNTER'
    AND tab.TABLE_TYPE = 'BASE TABLE'
  )
  AND EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
    WHERE tc.TABLE_NAME = col.TABLE_NAME
    AND tc.TABLE_SCHEMA = col.TABLE_SCHEMA
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND ccu.COLUMN_NAME = 'COUNTER'
  );

EXEC sp_executesql @sql 



 
 
SET @sql = '';
SELECT @sql = @sql + '
INSERT INTO #TABLOBILGILERI (TABLOADI, DEGER, SAYISI, ALANADI)
SELECT ''' + col.TABLE_NAME + ''' AS TABLOADI,EVRAK_TIPI,COUNT(*),''EVRAK_TIPI 0 DEĞERİNİ ALAMAZ MUTLAKA BİR DEĞER YAZILMASI LAZIM  (SELECT *   FROM dbo.fn_Bln_Evraklar(0) bu sorgu ile bütün fiş türleri gelmektedir. İç Select kullanıdığı için hata verir.''
    FROM ' + col.TABLE_SCHEMA + '.' + col.TABLE_NAME + '
	WHERE EVRAK_TIPI=0
    GROUP BY EVRAK_TIPI
    HAVING COUNT(*) >=1'
FROM INFORMATION_SCHEMA.COLUMNS col
WHERE col.COLUMN_NAME = 'EVRAK_TIPI' AND col.TABLE_NAME NOT IN ('STOK_HAR_OZET')
 

EXEC sp_executesql @sql 


INSERT INTO #TABLOBILGILERI (TABLOADI, DEGER, SAYISI,ALANADI)

SELECT 'EVRAK_TURLERI',ENO,2 ,'ENO Evrak tiplerinin bulunduğu tablo burada aynı eno 2 tane olursa hata verir.' 
FROM fn_Bln_Evraklar(0)
GROUP BY ENO HAVING(COUNT(*)>1)


INSERT INTO #TABLOBILGILERI (TABLOADI, DEGER, SAYISI,ALANADI)
select 'TICARI_PARAMETRELER',PARAMID,COUNT(*),'PARAMID'
from TICARI_PARAMETRELER
GROUP BY PARAMID  HAVING(COUNT(*)>1)

SELECT * 
FROM #TABLOBILGILERI ORDER BY SAYISI DESC;