DB scope with system SPs and INFORMATION_SCHEMA views

DB scope with system SPs and INFORMATION_SCHEMA views

Post 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

 
 
 

DB scope with system SPs and INFORMATION_SCHEMA views

Post 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


 
 
 

1. SPs queryng Views vs. SPs querying Joined Tables

Hi All,

We're querying our DB through the use of Stored Procs and I noticed
not long ago that no-one had been using views up until that point.
They had simply been using JOIN in the SQL statement right in the
Stored Proc.  My question is this...

In terms of performance, which is better.
(1) Querying a View from the Stored Proc.
(2) Query the tables in question by using JOINs in the Stored Proc.

Obviously part of the reason to use SPs or VIEWs is to allow the
server to optimize it for you.  Therefore, I didn't know if there was
any point to creating views and then modifying the SPs to query the
views instead of joining tables.

Thanks.

I hope I was clear

--Ed

2. Developer 2000 vs 6i certification

3. Select within a group

4. Field not found error

5. hiding system tables/views/sps

6. SQL Server Beta

7. Stored Procedure to create Views and SPs in another DB

8. system table/db that stores SPs?

9. Calling SPs from views / views with parameters

10. Permissions problem with INFORMATION_SCHEMA views

11. VIEW returns no values when using INFORMATION_SCHEMA