Database Bazlı Tablo ve Kolon Bilgilerine Ulaşma

Merhaba Sevgili Arkadaşlar,

Aşağıda ki scripti çalıştırarak Database imizin üzerindeki tüm Tablo ve Kolon Bilgilerine ulaşabilirsiniz….

-- Yusuf KAHVECİ
-- www.yusufkahveci.com
[email protected]

use SQLTURKIYE_Test
declare @SuspectTableColumnsT table
(
  SchemaName sysname not null,
  TableName sysname not null,
  ColumnName sysname not null
);

declare @TabloKolonBilgileri table
(
  SchemaName sysname not null,
  TableName sysname not null,
  ColumnName sysname not null,
  TypeName sysname not null
);

declare @TabloDetail table
(
  SchemaName sysname not null,
  TableName sysname not null,
  ErrorMessage varchar(1024) not null
);

insert into @TabloKolonBilgileri
select s.name, t.name, c.name, ty.name
from sys.types ty
  join sys.columns c on c.user_type_id = ty.user_type_id
  join sys.tables t on c.object_id = t.object_id
  join sys.schemas s on t.schema_id = s.schema_id
where c.is_nullable = 1

declare @SchemaName sysname;
declare @TableName sysname;
declare @ColumnName sysname;
declare c1 cursor local fast_forward
for
  select SchemaName, TableName, ColumnName FROM @TabloKolonBilgileri
  open c1
  fetch next from c1
  into @SchemaName, @TableName, @ColumnName
  while @@FETCH_STATUS = 0
  begin
    declare @b bit;
    declare @sql nvarchar(1024);

    begin try
      set @sql = N'set @b = (case when exists(select 1 from ' + @SchemaName + '.' + @TableName + ' where ' + @ColumnName + ' is null) then 1 else 0 end);';
      exec sp_executesql @sql, N'@b bit output', @b output;

      if (@b = 0)
      begin
        insert into @SuspectTableColumnsT
        select @SchemaName, @TableName, @ColumnName;
      end;
    end try
    begin catch
      if (not exists(select 1 from @TabloDetail where SchemaName = @SchemaName and TableName = @TableName))
      begin
        insert into @TabloDetail
        select @SchemaName, @TableName, ERROR_MESSAGE();
      end
    end catch

    fetch next from c1 into @SchemaName, @TableName, @ColumnName;
  end
  close c1;
  deallocate c1;

select * from @SuspectTableColumnsT order by SchemaName, TableName;
--select * from @TabloDetail order by SchemaName, TableName;

 

Yukarıda ki Scripti çalıştırdığımda kendimde çıkan sonucun ekran görüntüsü aşağıdadır.Görüdğünüz üzere Tblolarım ve Kolon ları listelenmektedir.

tableDetail-197x300

 

ykkrtvzt

Leave a Reply

Your email address will not be published. Required fields are marked *