Very Computer

by **John Beatt** » Wed, 04 Feb 2004 23:05:55

Hi -

Is there a way to keep the DB scope when using INFORMATION_SCHEMA views? I
currently construct a string using DB_NAME() for full naming convention and
execute that.

The following system SP illustrates the problem:

CREATE PROCEDURE sp_a
AS
SET NOCOUNT ON

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY 1

SELECT name
FROM sysobjects
WHERE type = 'U'
ORDER BY 1

'SELECT TABLE_NAME

WHERE TABLE_TYPE = ''BASE TABLE''
ORDER BY 1'

SET NOCOUNT OFF
GO

by **John Beatt** » Thu, 05 Feb 2004 00:30:45

Anybody else ever notice that system SPs don't carry the scope of the
calling DB when using INFORMATION_SCHEMA views? Is there any other way than
string construction with DB_NAME() to reference the calling DB's scope?

Thanks,
John

> Hi -

> Is there a way to keep the DB scope when using INFORMATION_SCHEMA views?
I
> currently construct a string using DB_NAME() for full naming convention
and
> execute that.

> The following system SP illustrates the problem:

> CREATE PROCEDURE sp_a
> AS
> SET NOCOUNT ON

> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> ORDER BY 1

> SELECT name
> FROM sysobjects
> WHERE type = 'U'
> ORDER BY 1

> 'SELECT TABLE_NAME

> WHERE TABLE_TYPE = ''BASE TABLE''
> ORDER BY 1'

> SET NOCOUNT OFF
> GO

Very Computer

DB scope with system SPs and INFORMATION_SCHEMA views

DB scope with system SPs and INFORMATION_SCHEMA views

DB scope with system SPs and INFORMATION_SCHEMA views