dynamic sql to get tables in a db

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

dynamic sql to get tables in a db

Author

Topic

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-03-12 : 16:13:28

I am creating an online reporting system that allows the user to create a custom report using any database, table and view available to him her.

1. they select a database (from an intranet webpage) from a list I create using EXEC sp_databases

2. based on this selection, I then wanted to create a stored procedure that would return the tables and views in that selected database via a dyamically generated sql statement like this:

CREATE PROCEDURE getAvanteSources @listdb sysname
AS

SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @execstring varchar(1024)
SET @execstring = ''

--SET @execstring = 'SELECT * FROM ' + @listdb + '..sysobjects WHERE type = ''V'' OR type = ''U'' ORDER BY type, [name]'
SET @execstring = 'SELECT [name] FROM ' + @listdb + '..sysobjects'

EXECUTE @execstring

However, for some reason I get this message when I try to run the stored procedure:

Server: Msg 203, Level 16, State 2, Procedure getAvanteSources, Line 15
The name 'SELECT [name] FROM INTRANET..sysobjects' is not a valid identifier.

If I return the dynamically generated select statement, I can manually execute it with no problems.

The error message does not make sense. I thought it might be a permissions problem but can't see how. The login id used has complete access to all the databases on that server.

Anyone got some ideas on resolving this???

All help appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-12 : 16:19:25

Try changing the line to read:

EXECUTE (@execstring)

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-03-12 : 16:58:26

geez, Rob, sorry about that. my brains (whats left) are turning to mush the last couple of days.

thx much for the assistance.

[much bowing and scraping]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-12 : 17:02:19

No problem, done it myself a number of times

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources