question

sdh96 avatar image
sdh96 asked

i need to select all tables in my database that have a common field called order_no where order_no equals 905210. I have queried all tables in my schema that have the field order_no and it is about 70 tables. Steve

i need to select all tables in my database that have a common field called order_no where order_no equals 905210. I have queried all tables in my schema that have the field order_no and it is about 70 tables. Steve
sqltables
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Does anyone know how to query the entire database for all tables with the field order_no in it and where the order number equals 905210? Although 70 tables contain the field order_no, not all 70 will have that order number 905210 in it.
0 Likes 0 ·
This web site runs on voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You could dynamically build up a string containing queries to run against the 70 tables. Something like this: declare @s nvarchar(max)=''; declare @desiredvalue int=905210; select @s = @s + 'SELECT ''' + quotename(s.name) + '.' + quotename(t.name) + ''' as TableName,* FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WHERE ' + quotename(ac.name) + ' = ' + cast(@desiredvalue as varchar(10)) + '; ' from sys.tables t inner join sys.all_columns ac ON t.object_id = ac.object_id and ac.name='order_no' inner join sys.schemas s ON t.schema_id = s.schema_id print @s exec sp_executesql @s
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

emil87b avatar image
emil87b answered
This is my search procedure modified to suit your reqirements DECLARE @String NVARCHAR(200)= '905210' CREATE TABLE ##Search (ID INT IDENTITY PRIMARY KEY, PrimaryKey NVARCHAR(200), PrimaryColumnName NVARCHAR(200), TableName NVARCHAR(200), ColumnName NVARCHAR(200)) CREATE TABLE #T (ID INT IDENTITY PRIMARY KEY, TableName NVARCHAR(200), ColumnName NVARCHAR(200), PrimaryColumnName NVARCHAR(50)) INSERT INTO #T(TableName, ColumnName, PrimaryColumnName) SELECT C.TABLE_NAME, C.COLUMN_NAME, CU.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME = C.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON CU.TABLE_NAME = C.TABLE_NAME AND CU.TABLE_SCHEMA = C.TABLE_SCHEMA AND CU.CONSTRAINT_NAME like 'PK%' WHERE DATA_TYPE != 'sql_variant' AND C.TABLE_SCHEMA = 'dbo' AND T.TABLE_TYPE = 'BASE TABLE' AND C.COLUMN_NAME = 'order_no' DECLARE @i INT DECLARE @max INT DECLARE @SQL NVARCHAR(MAX) DECLARE @TableName NVARCHAR(200),@ColumnName NVARCHAR(200),@PrimaryColumnName NVARCHAR(200) SELECT @i = MIN(ID), @max = MAX(ID) FROM #T WHILE @i <= @max BEGIN SELECT @TableName = TableName, @ColumnName = ColumnName, @PrimaryColumnName = PrimaryColumnName FROM #T WHERE ID = @i SET @SQL = 'IF EXISTS (SELECT * FROM ['+@TableName+'] WHERE ['+@ColumnName+'] like ''%'+@String+'%'') INSERT INTO ##Search(PrimaryKey, PrimaryColumnName, TableName, ColumnName) SELECT '+ISNULL(@PrimaryColumnName,'''''')+' AS PrimaryKey, '''+ISNULL(@PrimaryColumnName,'')+''' AS [PrimaryKeyColumnName], '''+@TableName+''' AS [TableName], '''+@ColumnName+''' AS [StringFoundInColumn] FROM ['+@TableName+'] WHERE CONVERT(NVARCHAR(200),['+@ColumnName+']) like ''%'+@String+'%''' EXEC (@SQL) SET @i += 1 END SELECT ID, PrimaryKey, PrimaryColumnName, TableName, ColumnName FROM ##Search DROP TABLE #T DROP TABLE ##Search
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.