Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type = "'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.How about this?
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE =3D 'BASE TABLE'
-- Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
> > CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type ==3D
> "'Table'"
> > it won't do it as it can only work in its own context. I have also =tried
> using the use command with a database name as a parameter to point it =at the
> database. It won't let me do that either. Any ideas, Regards.
> >|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:
>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at the
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)