Enumerate all tables in a server without dynamic sql.

Hi, I'm new to SQL and have a question. Is there any way to programatically enumerate all tables on a server without resorting to dynamic sql?

What I would like to do, in pseudocode is:

for each database db in master.sys.databases

for each table tbl in db.sys.tables

insert tbl into @.table variable

So far from what I've read, it looks like this require dynamic SQL. The only problem is that I'm not allowed to use dynamic SQL for this project! Any help is appriciated!

Thanks

If you want a .NET way to do this, check out SMO (SQL Management Objects). It seems to be the same backbone in which the SQL Management Studio was built upon.|||

There's the undocumented

sp_MSForEachDB

and

sp_MSForEachTable

|||

Just a note: You can't create a table variable with an [ SELECT... INTO ... ]

You will have pre-define the table variables,

or [ SELECT ... INTO #Temp ] tables.

|||

Sorry, didn't see this was a TSQL forum. The undocumented foreach would work, but would be dynamic.

Code Snippet

EXEC sp_MSForeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES'

The ? is a way to represent the name of the database. This returns multiple result sets, but is the best you are going to get out of the box. Mainly, there is no consolidated metadata for the server. Each database hosts its own metadata.

|||

Nope, there is not way to do this. The ForEachDB thing that other suggest is a way to do it, but it would in fact be dynamic SQL

Why no dynamic SQL? If this is a production piece of code that has to be run multiple times, this is very much baffling to me.

Not even sure why you would want to do this. Can you explain what you are trying to do?

|||

Basically, I'm an intern at MS. I can't go into specifics of what I'm working on (NDA!), but it has to do with automating auditing tasks. The rest of my team is working on the main function and I'm writing a wrapper that needs to call that function on every Database, Table, and Object on the server. Because of the security risks, I was told no dynamic SQl... But that appears to be the only way to do it! What a conumdrum!

Thanks for the advice anyway!

|||

Here's the best way in my opinion...

Use SMO (SQL Management Objects) to enumerate through the tables in the server. Wrap this code into a .NET-based SQL Function. This, of course, assumes you can use SQL CLR for your solution. Otherwise, you will not be able to accomplish what you want to accomplish without dynamic SQL.

|||

Are you sure you aren't just being hazed? This sounds like something I would tell an intern if I had one to mess with

Seriously, this is a wierd stance considering that all of the tools that Microsoft builds use dynamic sql from the tool. SSMS does for sure. And, considering that the tool owns the code, not sure how security comes into play.

I agree with the SMO answer personally, if you aren't writing this in T-SQL directly, which you really couldn't. In 2005, using EXECUTE AS and proper use of QUOTENAME() you can mitigate the issues with security, if you are trying to write a stored procedure.