none
SQL Query to predict column

    Question

  • Hello Team,

    we are planning to do a task  i..e to identify the tables which have column like firstname, lastname etc

    This i can achieve by writing sql query but there are tables which won't have any proper column name but data is like firstname and lastname.

    So am looking if is there any easy way to predict the columnname using the data ?


    Wednesday, September 25, 2013 12:48 AM

All replies

  • Try the below method,

    declare @temp table (TableName varchar(30),ColName varchar(30))
    insert into @temp
    select a.name TableName,b.name ColumnName from sys.objects a 
    inner join sys.columns b on a.object_id=b.object_id
    where a.type='U' and (b.name like '%Name%' or b.name = 'FirstName' or b.name = 'LastName') 
    -----------
    declare @tab varchar(30),@col varchar(30),@sql varchar(300)
    declare c cursor for select * from @temp
    open c
    fetch next from c into @tab,@col
    while @@fetch_status = 0 
    begin
    	set @sql = 'select [' + @col + '] from [' + @tab + '] where charindex('','', [' + @col + '],1) >= 1'
    	set @sql = 'if exists(' + @sql + ') print 1 else print 0'
    	exec(@sql)
    	fetch next from c into @tab,@col
    end
    close c
    deallocate c


    Regards, RSingh

    Wednesday, September 25, 2013 2:43 AM