SQL Query to predict column


  • 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 TableName, ColumnName from sys.objects a 
    inner join sys.columns b on a.object_id=b.object_id
    where a.type='U' and ( like '%Name%' or = 'FirstName' or = '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 
    	set @sql = 'select [' + @col + '] from [' + @tab + '] where charindex('','', [' + @col + '],1) >= 1'
    	set @sql = 'if exists(' + @sql + ') print 1 else print 0'
    	fetch next from c into @tab,@col
    close c
    deallocate c

    Regards, RSingh

    Wednesday, September 25, 2013 2:43 AM