How do I select from tables with a specifically named column when that column contains a specific value?

Question

I am trying to select all the tables that has the same column, and there is an instance in that column matches a string format that I specify.

For example,

Table1:

FirstName, LastName, ID

Table2:

ID, Value

Table3:

FirstName, Value

I want my result to show the tables that contains ID in which the ID begin with character 'a'

So far what I have is

    SELECT SYS.TABLES.NAME FROM SYS.TABLES
    INNER JOIN SYS.COLUMNS
    ON SYS.TABLES.OBJECT_ID = SYS.COLUMS.OBJECT_ID
    WHERE SYS.COLUMS.NAME = 'ID'

but then I have no clue how to continue. Any help is appreciated :)

You might want to have a look at this SO post - [how to set table name in dynamic sql query?](https://stackoverflow.com/questions/20678725/how-to-set-table-name-in-dynamic-sql-query) — Marek Vitek, Jul 06 '17 at 18:51
And this might be second piece of puzzle you are looking for [T-SQL loop over query results](https://stackoverflow.com/questions/11852782/t-sql-loop-over-query-results) Just in case you are not familiar with it. I guess you will be able to put it together. — Marek Vitek, Jul 06 '17 at 19:03
Why include Table3 in your example, which lacks the column you're targeting (ID)? — Stan Shaw, Jul 06 '17 at 19:12

Stan Shaw · Accepted Answer · 2017-07-07T16:22:50.810

This code is tested and it works:

Create a cursor that selects all of the table/column names where column = ID (we didn't need a column variable since you are only targeting 'ID', but I included it in case you or someone else needs more functionality).

Once you have that information stored, you can loop through the cursor and execute the dynamic SQL. Note that this is susceptible to SQL-injection, as is the case with practically all dynamic SQL.

This approach will give you a separate datatable for each table that has a column ID - even if there are no ID columns that meet your conditions (you'll just get a blank datatable for those).

Also, you can change the size of the variables as needed.

Please let me know if you need any clarification or modifications.

 DECLARE @TableName varchar(255), @ColumnName varchar(255), @SQL varchar(1000)

 DECLARE TempCursor CURSOR FOR
 SELECT T.[name] AS [TableName], C.[name] AS [ColumnName] 
 FROM sys.tables T 
 JOIN sys.columns C 
    ON T.object_id = C.object_id 
 WHERE C.[name] = 'ID' 


 OPEN TempCursor
 FETCH NEXT FROM TempCursor INTO @TableName, @ColumnName
 WHILE @@FETCH_STATUS = 0
    BEGIN       

        SET @SQL = 'SELECT * FROM ' + @TableName
        SET @SQL = @SQL + ' WHERE ' + @ColumnName + ' like ''a%'''
        EXECUTE (@SQL)

        FETCH NEXT FROM TempCursor INTO @TableName, @ColumnName

    END

CLOSE TempCursor
DEALLOCATE TempCursor

@swordgit No problem - It admittedly took me a couple of tries, but we got there! — Stan Shaw, Jul 07 '17 at 14:10
@swordgit Also, you don't have to do it for me, but in the future, you should upvote any questions that help you so that future users with similar problems can quickly find the best answer. — Stan Shaw, Jul 07 '17 at 14:12

gymni f · Answer 2 · 2017-07-06T19:36:23.167

0

You can use "LIKE" to select once beginning with 'a':

SELECT SYS.TABLES.NAME AS t
FROM SYS.TABLES INNER JOIN SYS.COLUMNS
ON SYS.TABLES.OBJECT_ID = SYS.COLUMS.OBJECT_ID 
WHERE SYS.COLUMNS.NAME = 'ID' AND EXISTS (SELECT * FROM t WHERE t.ID LIKE 'a%');

The symbol "%" indicates that any combination of characters can be present after the letter "a"

edited Jul 06 '17 at 19:36

answered Jul 06 '17 at 18:38

gymni f

398
2
14

1

SYS.COLUMNS.NAME only refer to the name of the column but not the values that the column contains, so it does not work, I am looking for the columns that contains value which starts from letter a – swordgit Jul 06 '17 at 18:47
1

I am afraid that this is not what OP asked for. If I understand it correctly, he wants list of all tables where in column ID is record beginning with 'a'. Some kind of DB-wide Like. – Marek Vitek Jul 06 '17 at 18:47
I'm sorry I misunderstood your question @swordgit, can you post the code with names similar to your ones? It would help me to understand better – gymni f Jul 06 '17 at 18:50
@gymnif sorry I don't have the access to the actual code constructing the table. Marek is right, I want to list all tables that have a column named ID and that column of that table has at least a record starting from letter 'a' – swordgit Jul 06 '17 at 19:22

score 0 · Answer 3 · answered Jul 06 '17 at 19:26

I still don't know if i got your question properly - but let's try!

Use the following query to get all tables with a column ID

SELECT SYS.TABLES.NAME , SYS.COLUMNS.NAME
FROM SYS.TABLES INNER JOIN SYS.COLUMNS
ON SYS.TABLES.OBJECT_ID = SYS.COLUMNS.OBJECT_ID 
WHERE SYS.COLUMNS.NAME = 'ID';

You will have to iterate through the table names (think cursor, while etc.)

Inside the loop, try something like this

declare @resultcount int;
declare @QueryMain as nvarchar(4000)
set @QueryMain = N'SELECT * FROM <TABLE_NAME> WHERE ID LIKE ''a%'''
exec sp_executesql @QueryMain
set @resultcount = @@rowcount;

Inspect the value of @resultcount to see if the current table name qualifies and use something like a temporary table to collect it.

You may be use other options in sp_executesql to tweak this query

How do I select from tables with a specifically named column when that column contains a specific value?

3 Answers3