Iterating through tables

I want to create a stored procedure that will list all the tables for a
given database. I can get the databases using the sp_database function, but
the sp_tables requires that I be in database to use it. I want to create a
stored procedure that will take in the database name as a variable and
return a list of the tables for that database. I need to do this so as new
databases and tables are added and removed my program will have the up to
date data for the database. thanks.
JohnDECLARE @.dbname NVARCHAR(128), @.sql NVARCHAR(4000)
SET @.dbname = 'pubs'
SET @.sql =
'SELECT [table_name]
FROM '+QUOTENAME(@.dbname)+'.[information_schema].[tables]'
EXEC (@.sql)
David Portas
SQL Server MVP
--|||>> take in the database name as a variable and return a list of the tables f
or that database. <<
Have you considered that this is a METADATA problem and should be done
with sp_ routines and never in your own stored procedure? This will
lead to kludgfes with dynamic SQL, etc.
Gee, do you do that a lot? Do your users get to create tables, columns
or entire databases on the fly? Your whole approach sounds screwed up.
Would you like to expalin what you are doing so you can get a few
thousand dollars of freee consulting or do you just want a stinking
dirty little kludge?
No, your data dictionary should contain this kinfd of information, not
the programs.