Query to get Table Names by specifying database

[VBCrazyCoder]

Hi all,

I am trying to write a query for a stored procedure that will allow me to pass in the database name and it will return a list of tables in that database. I know I can do a exec sp_tables but I will not know which database I need to execute this on. Essentially I want something like this: select * from [specifryDBhere].sysobjects where xtype IN ('U', 'S', 'V') order by name asc

Is there a table in master that lists all tables in all databases? How else can I do this in the stored procedure?

Thanks for any assistance!

[szlamany]

This link shows a post

http://www.vbforums.com/showpost.php...67&postcount=8

that loops through the sysobjects table looking for indexes, views and procedures.

We use it to DROP certain items before sending a DB to a data warehouse.

The tables should show up in sysobjects also with a certain "type" - run a loop on your database (without the DROPS of course) and see what you get.

[szlamany]

Actually this select appears to do the trick as well.

SELECT * FROM SYSOBJECTS WHERE TYPE='U'

[VBCrazyCoder]

Yes, that works but for whatever database I am currently in. But I want to specify what database to look in so I get the tables in that database. Is there a master listing of tables that are in all databases on the server or can I somehow specify as a parameter to my stored proc which database to do the query against?

[szlamany]

The objects are stored in the DB only (AFAIK).

I usually suggest not to use DYNAMIC SQL - but I cannot think of a way around it...

USE ACCTFILES
GO
DROP PROCEDURE SHOWTABLES
GO
CREATE PROCEDURE SHOWTABLES @DB VARCHAR(100) AS
BEGIN
	DECLARE @SQL VARCHAR(100)
	SET @SQL='SELECT * FROM ['+@DB+'].[DBO].[SYSOBJECTS] WHERE TYPE=''U'''
	EXEC (@SQL)
END
GO
EXEC SHOWTABLES 'ACCTFILES'
EXEC SHOWTABLES 'FUNDS'

[VBCrazyCoder]

Thanks, I will give it a shot.

[VBCrazyCoder]

Yup,

That is exactly what I was looking for - would have taken me a long long time to figure out the syntax - thanks very much for you assistance sz!

[VBCrazyCoder]

One more question - can't seem to figure out how to make the U in the query us a passed in parameter (I have never done dynamic sql before...)

This does not work - can't seem to get the syntax correct near the @OBJTYPE:

DECLARE @SQL VARCHAR(100)
DECLARE @OBJTYPE VARCHAR(1)
DECLARE @DB VARCHAR(100)
SET @DB = 'DNNameHere'
SET @OBJTYPE = 'U'

SET @SQL='SELECT * FROM ['+@DB+'].[DBO].[SYSOBJECTS] WHERE TYPE='@OBJTYPE''
EXEC (@SQL)

[si_the_geek]

Just do the same as was done for @DB, ie:
SET @SQL='SELECT * FROM ['+@DB+'].[DBO].[SYSOBJECTS] WHERE TYPE=' + @OBJTYPE + ''

..but as the + '' at the end doesn't do anything, you can do this:
SET @SQL='SELECT * FROM ['+@DB+'].[DBO].[SYSOBJECTS] WHERE TYPE=' + @OBJTYPE

[jcis]

I think something like this?
SET @SQL='SELECT * FROM ['+@DB+'].[DBO].[SYSOBJECTS] WHERE TYPE=''+@OBJTYPE+'''

[si_the_geek]

Good point - I forgot about the quotes around the text value

You need to double-up on the quotes inside a string tho, eg:

SET @SQL='SELECT * FROM ['+@DB+'].[DBO].[SYSOBJECTS] WHERE TYPE='''+@OBJTYPE+''''