CODEWITHSUNDEEP
sqlsql-server
jMiguel LA
jMiguel LA

Reputation: 350

Looping through a group of tables

I would like to figure out the best way in SQL Server to find some characters in some tables. Would you advise me which is the best way to loop through a group of tables/columns to find some characters using only SQL commands(stored procedures...)?

Thanks in advance.

Example:

  1. Selecting the group of tables/columns:
SELECT t.name AS table_name
, c.name AS column_name
--, ty.Name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
where ty.Name = 'uniqueidentifier'
  1. Output of the select
 table_name                 column_name
 formset_details_translation    formset_details_translation_id
 formset_details_translation    formset_version_id
 current_schema_instances       original_unique_guid

...
  1. Finding loop. How should I use? cursor?
for each Table
    For each Column
        select * 
        from tableI
        where columnI = 'XXXX...XXXX'
    End For
End For

Upvotes: 1

Views: 1378

Answers (2)

Tirthak Shah
Tirthak Shah

Reputation: 515

Is it something that you are looking at ?

DECLARE @cnt INT,@ID int = 1
Declare @TempTable table(ID int IDENTITY(1,1), table_name varchar(200), column_name varchar(200))

Insert into @TempTable
SELECT t.name AS table_name
, c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
where ty.Name = 'uniqueidentifier'

Select @cnt =  count(1) from @TempTable
Declare @sql varchar(max), @table_name varchar(200), @column_name varchar(200) 
WHILE @ID <= @cnt
BEGIN

 SET @sql = ''
 SET @table_name = ''
 SET @column_name = ''
 Select @table_name = table_name,@column_name = column_name from @TempTable where ID = @ID
 SET @sql = 'select * from ' + @table_name + ' where ' + @column_name + ' = 123' --You can replace 123 by your text

 PRINT (@sql) -- You can write EXECUTE (@sql) here to execute it


 SET @ID = @ID + 1
END

Upvotes: 2

AdamL
AdamL

Reputation: 13191

It requires some manual work, but you could something like this:

select
    TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
    cmd = 'select ''' + table_name + ''' as tabname, ''' + column_name + ''' as colname, count(*) as rows from [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA  + '].[' +  TABLE_NAME + '] where [' + COLUMN_NAME + '] like ''%%'' union all '
from INFORMATION_SCHEMA.COLUMNS
where 1=1
    and DATA_TYPE like '%char'
    and TABLE_NAME like '%%'

Then you just execute the resulting query (change the select to fit your needs better and remove the last union all). The results for every column in the same table will be in different rows, but you could use FOR XML PATH to generate OR statements so it doesn't return same rows duplicated by searched columns.

If you need this to be dynamic, without copying and pasting, check this undocumented procedure out:

sp_msforeachtable @command1="declare @x Nvarchar(255); set @x='select count(*) from ?'; execute sp_executesql @x"

Build your queries in @command1 using information_schema.columns, like in previous example.

Upvotes: 1

Related Questions