none
getting all tables containing a special column with specific data

    Question

  • since days I try to create a script that gives me a list of tablename for all tables containing a specific column that contains specific data.

    I get all tables having the column. but anytime, even if the specific value is not in the table.

    Best would be to get running in an SSIS Package..because after getting the list I have to copy all these tables from one server instance to another (so SSIS DataFlow seems to be the easiest to do so)

    Wednesday, July 24, 2013 8:10 PM

Answers

  • Hi,

    I created a script for your requirement, and it works. Replace ColumnName with the actual column that you intend to check.

    DECLARE @SQL VARCHAR(MAX)
    SET @SQL = ''
    select @SQL = @SQL + 'select * from (select count(*) as cnt,'''+tab+'.'+name+''' AS tab from '+tab+' where '+name+' = ''2013-01-17 15:03:25.000'')A where cnt >0 UNION ALL ' from (
    SELECT OBJECT_NAME(OBJECT_ID) as tab ,* FROM SYS.columns WHERE name = 'ColumnName')A
    SET  @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-10)
    EXEC(@SQL)
    Let us know if you have any issues.

    Rajkumar

    Thursday, July 25, 2013 8:55 AM

All replies

  • What is question here please ?

    You want the script and data flow steps or just a general discussion you want to share ?


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 24, 2013 11:06 PM
  • Well if possible I would like get some help and advice

    Here my part of the script which in the end generates table even if the specific data is not in the columns

    DECLARE @SourceDB AS NVARCHAR(128)
    DECLARE @TargetDB AS NVARCHAR(128)
    DECLARE @ColName As NVARCHAR(128)
    DECLARE @ColValue AS NVARCHAR(4)
    DECLARE @ColValue2 AS NVARCHAR(4)
    DECLARE @ColValue3 AS NVARCHAR(4)
    
    DECLARE @mybin1 varbinary(5)
    SET @mybin1 = 0x27
    
    
    SET @TargetDB = 'DB_AXFRTA'
    SET @ColName = 'DataAreaId'
    SET @ColValue = 'dat'
    SET @ColValue2 = 'frta'
    SET @ColValue3 = 'chxa'
    
    
    DECLARE @T TABLE (TableName NVARCHAR(128))
    DECLARE @S TABLE (SQLStatement NVARCHAR(MAX))
    INSERT @T(TableName)
     SELECT so.name from sysobjects so INNER JOIN syscolumns sc on so.id = sc.id
      WHERE sc.name = @ColName
        AND so.type = 'U'
    
    INSERT INTO @S(SQLStatement)
     SELECT 'SELECT * INTO '+ @TargetDB + '.dbo.' + TableName + ' FROM '  + TableName + ' WHERE ' + @ColName + ' = ' + CONVERT(varchar(5), @mybin1) + @ColValue + CONVERT(varchar(5), @mybin1) + ' OR ' + @ColName + ' = ' + CONVERT(varchar(5), @mybin1) + @ColValue2 + CONVERT(varchar(5), @mybin1) + ' OR ' + @ColName + ' = ' + CONVERT(varchar(5), @mybin1) + @ColValue3 + CONVERT(varchar(5), @mybin1) FROM @T
    
    DECLARE @sqlExecute varchar(max)
    DECLARE MynewCursor CURSOR -- Declare cursor name
    
    FOR SELECT * from @S
    
    Open MynewCursor
    
    FETCH NEXT FROM MynewCursor INTO @sqlExecute
    EXEC(@sqlExecute)
    
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
    
    FETCH NEXT FROM MynewCursor
    INTO @sqlExecute
    EXEC(@sqlExecute)
    
    END
    
    CLOSE MynewCursor
    DEALLOCATE MynewCursor
    

    noq I want to create and insert the tables only if there is data.

    And if possible running on a SSIS Package because the Database are on different servers

    Thursday, July 25, 2013 3:44 AM
  • Hi,

    I created a script for your requirement, and it works. Replace ColumnName with the actual column that you intend to check.

    DECLARE @SQL VARCHAR(MAX)
    SET @SQL = ''
    select @SQL = @SQL + 'select * from (select count(*) as cnt,'''+tab+'.'+name+''' AS tab from '+tab+' where '+name+' = ''2013-01-17 15:03:25.000'')A where cnt >0 UNION ALL ' from (
    SELECT OBJECT_NAME(OBJECT_ID) as tab ,* FROM SYS.columns WHERE name = 'ColumnName')A
    SET  @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-10)
    EXEC(@SQL)
    Let us know if you have any issues.

    Rajkumar

    Thursday, July 25, 2013 8:55 AM