How to list all tables in a database by giving par

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 2005 Forums

Transact-SQL (2005)

How to list all tables in a database by giving par

Author

Topic

sunilsi
Starting Member

21 Posts

Posted - 2008-11-26 : 02:37:48

Hi all,

I need to list all tables in a database by giving parameter as database name to stored procedure. I tried the following, but it never works. Please help.

ALTER Procedure [dbo].[sp_GetTables]
(
@dbname nvarchar(200)--,
--@tablename varchar(128) output
)
As

DECLARE @SQL nvarchar(1000)
SET @SQL = 'Declare @dbname varchar(128);SELECT name FROM '+ '@dbname' +'..sysobjects where type =''s'' or type = ''u'''

Execute sp_executesql @SQL
--EXEC (@SQL)
RETURN

Error while executing

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '.'.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-26 : 02:55:31

CREATE PROCEDURE [dbo].[sp_GetTables]
(
@DatabaseName VARCHAR(50)
)
AS
BEGIN
DECLARE @SQLtext VARCHAR(100)
SELECT @sqlText= 'USE '+@DatabaseName
EXEC (@sqlText)

SELECT sys.schemas.name +'.'+sys.objects.name AS table_name FROM sys.schemas INNER JOIN sys.objects
ON sys.schemas.schema_id = sys.objects.schema_id WHERE type ='U' ORDER BY sys.schemas.schema_id

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 03:09:28

you cant change databse context like this inside sp. you could however use undocumented sp sp_msforeachdb for this

CREATE PROCEDURE [dbo].[GetTables]
@DatabaseName VARCHAR(50)
AS
BEGIN

DEclare @Sql varchar(8000)

SET @Sql='EXEC sp_msforeachdb ''IF ''''?'''' LIKE '''''+ @DatabaseName + '%'''' SELECT * FROM INFORMATION_SCHEMA.TABLES'''
EXEC (@Sql)
END
GO

sunilsi
Starting Member

21 Posts

Posted - 2008-11-26 : 04:02:19

Friends,

thanks for the replies... Still I endup with below error...

Msg 911, Level 16, State 1, Procedure sp_GetTables, Line 14

Could not locate entry in sysdatabases for database 'Declare @dbname sysname;SELECT name FROM @dbname'. No entry found with that name. Make sure that the name is entered correctly.

what might be the reason?... Plz help...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 04:17:35

quote:
Originally posted by sunilsi

Friends,

thanks for the replies... Still I endup with below error...

Msg 911, Level 16, State 1, Procedure sp_GetTables, Line 14

Could not locate entry in sysdatabases for database 'Declare @dbname sysname;SELECT name FROM @dbname'. No entry found with that name. Make sure that the name is entered correctly.

what might be the reason?... Plz help...

show code please

sunilsi
Starting Member

21 Posts

Posted - 2008-11-26 : 04:21:37

Friends,

Tried it this way & it works!... Thanks alot for you guys help....

Alter Procedure [dbo].[sp_GetTables]

( @dbname nvarchar(200) )

As
begin

DECLARE @SQL nvarchar(1000)

SET @SQL = 'use '+ @dbname +' ; SELECT name FROM sys.sysobjects where type =''s'' or type = ''u'''

--print @SQL

Execute sp_executesql @SQL --EXEC (@SQL) RETURN

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 04:23:52

did you my suggestion?

ALso dont use sp_ for sp names. this will cause cache miss sometimes and results in poor performance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 04:31:18

more details here

http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html

sunilsi
Starting Member

21 Posts

Posted - 2008-11-26 : 05:24:31

Thanks alot Visakh...
Also, I tried your suggestion & that works as expected...

Regards,
Sunil