CODEWITHSUNDEEP
c#sqlsql-serversql-server-2016
Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

Find all table's name in one Query or SQL command

I would like to find all table's names in my T-SQL command. My command can be a Select, Update, Delete, Insert, Merge or Truncate.

I can use C#. But I don't really how can I find them because there is a lot of possibility.

For example: I can have a select like select below:

SELECT
    <Schemaname>.<TableName1>.Field1,
    <TableName2>.Field2,
    Field3,
    Field4 = ( Select .. FROM <TableName6> WHERE ... )
FROM 
    <TableName1> , <TableName2>
INNER JOIN 
    <TableName3> AS TableName4 ON .....
WHERE ....
    <TableName2>.Field3 in ( SELECT ... FROM TableName5 )

The list that I am looking should has these table's names

TableName1,TableName2,TableName3,TableName5,TableName6

TableName4 is an alias name in this case and it does not present a real name of a table in database.

I have the command where I have used a table name with schema name and an other table without schema name and also I have the alias for some of my tables and the alias can be like a table name that I have really in my database.

Upvotes: 1

Views: 250

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

Doing this purely with t-sql is incredibly difficult. And by difficult I mean nearly impossible. It will take days and days to get a t-sql script that even comes close to being accurate for this. There are just too many possibilities. Unless the table name you are looking for is so unique you would feel comfortable just searching your code for that table name. Anything else is only going to get you close. Good luck!!!

This is impossible to get 100% correct and exhaustive. What happens if you have a function? And that function pulls data from a view? And that view pulls from other tables?

And even in your example above there are SO many possibilities. You would be better off creating this as a stored procedure and then looking at sys.depends.

But even that isn't exhaustive as it only goes as deep as this query, not the other objects dependencies being referenced

Upvotes: 1

Bertrand Leroy
Bertrand Leroy

Reputation: 145

Not too sure I really understand what you are trying to achieve but here are my 2 cents:

Assuming that you can extract the SQL command using some code (can't help if you need THAT code) from your many SSIS packages. Based on that, I would

  • use a reference list of all the database objects and search for each of them through your SQLCommand (extracted from your SSIS package)
  • Build a list of the all the name strings found (list of objects, could be tables, views, functions, stored procedures)

  • Then use that list to find the dependent objects in your DB (if you need to go that deep)

    select DISTINCT s1.class_desc, s1.object_id, referenced_major_id, OBJECT_NAME(s1.object_id) AS ObjName , OBJECT_NAME(s2.object_id) AS ObjName2 from sys.sql_dependencies s1 INNER JOIN sys.objects s2 ON s2.object_id = s1.referenced_major_id

  • Put all that into your result table

  • Move on to next SSIS package

Would that work for you?

B

Upvotes: 0

Related Questions