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? — Marek Vitek
– Marek Vitek, Commented Jul 6, 2017 at 18:51
And this might be second piece of puzzle you are looking for 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
– Marek Vitek, Commented Jul 6, 2017 at 19:03
Why include Table3 in your example, which lacks the column you're targeting (ID)? — Stan Shaw
– Stan Shaw, Commented Jul 6, 2017 at 19:12

Stan Shaw · Accepted Answer · 2017-07-07 16:22:50Z

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!
@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.

James Poulose · Accepted Answer · 2017-07-06 19:26:15Z

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

gymni f · Accepted Answer · 2017-07-06 19:36:23Z

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 6, 2017 at 19:36

answered Jul 6, 2017 at 18:38

gymni f

4084 silver badges14 bronze badges

4 Comments

swordgit Over a year ago

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

Marek Vitek Over a year ago

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.

gymni f Over a year ago

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

swordgit Over a year ago

@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'

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

4 Comments

Linked

Related