SQL Server Performance

INFORMATION_SCHEMA vs sysobjects

Discussion in 'General Developer Questions' started by RupertS, Oct 27, 2005.

  1. RupertS New Member


    I'm writing a stored procedure which will sit in master and can be called from any database. (So sp_MyProc in master then).

    So I want to check is a table exists in the local db.

    To demonstrate my issue, the statements below both sit in the master stored proc.

    So you would have thought that both statements below would return the same results, but I'm afraid not!

    1. select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @TableName

    2. SELECT name FROM sysobjects WHERE name=@TableName

    As number 1 always runs in the context of the master database where as number 2 will run in the context of the database in which the stored procedure is being from.

    Why is this?

    Especially as when I run no.1 in Query Analyser it returns the results of the database in which I'm using (not master).

    Thanks in advance for clearing this up for me!

    Rupert
  2. dineshasanka Moderator

  3. Adriaan New Member

    Quick and dirty way - doesn't check if the object name refers to a table or something else:<br /><br />IF OBJECT_ID(@TableName) IS NULL<br />BEGIN<br />SELECT 'Object does not exist!'<br />END<br /><br />If you look at INFORMATION_SCHEMA.TABLES, the database name is on the TABLE_CATALOG column so add the appropriate criteria.<br /><br />When querying sysobjects, refer to the table as MyDbName.dbo.sysobjects - it's not a regular table so you need to know a few things here and there [<img src='/community/emoticons/emotion-5.gif' alt=';)' />].
  4. RupertS New Member

    Try this to prove my point - that INFORMATION_SCHEMA.TABLES always runs in the master db context when run from stored procedure which sits in the master db.

    Rupert
    -----------------------------------------------------------


    use master
    go
    create proc sp_CheckForTable
    @table varchar(250)
    as
    select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @table
    SELECT name FROM sysobjects WHERE name=@table
    go
    use pubs
    go
    Print 'Check For Sales in INFORMATION_SCHEMA'
    select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'sales'
    go
    Print 'Check For Sales in Sysobjects'
    SELECT name FROM sysobjects WHERE name='sales'
    go
    Print 'Now check for both in stored Proc - notice how first query returns nothing!'
    go
    sp_CheckForTable 'sales'
    go
  5. Adriaan New Member

    One of the reasons why I never use the INFORMATION_SCHEMA views - everyone keeps telling you to use them, but they never give you an answer that you can work with.

    Use OBJECT_ID('MyDbName.dbo.MyTable') - that should work in the context of any database on the same server.
  6. satya Moderator

    http://www.devx.com/getHelpOn/10MinuteSolution/20561 for your reference, there is bit trouble with these views as these are tailor made and few times you may not get the results you want. MS suggests if there aren't any pre-supplied statements available then you may use querying system tables, but do not make this as a practice as they can be changed at anytime during SP or hotfix releases.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Madhivanan Moderator

    >>Try this to prove my point - that INFORMATION_SCHEMA.TABLES always runs in the master db context when run from stored procedure which sits in the master db.

    Because you created that sp in Master Database
    You should create it with the actual database you are working with

    Madhivanan

    Failing to plan is Planning to fail
  8. Adriaan New Member

    Madhivanan, the whole idea was to create a single sp that could be called from all databases.<br /><br />Given this limitation of the INFORMATION_SCHEMA views (yet another one [<img src='/community/emoticons/emotion-6.gif' alt=':(' />][xx(][:0]), also given the fact that you can put the database name in front of INFORMATION_SCHEMA and get the results in context, the only workaround seems to be dynamic SQL.<br /><br />I'm feeding the database name as a parameter, as I'm not entirely sure that you can rely on the DB_NAME() function here ... given the problem for which we're creating this workaround [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]...<br /><br />USE master<br />GO<br /><br />CREATE dbo.so_MyProc(@dbname VARCHAR(100))<br />AS<br /><br />EXEC ('SELECT * FROM '+ @dbname + '.INFORMATION_SCHEMA.TABLES')<br /><br />GO<br />
  9. Madhivanan Moderator

    Well. In that case Dynamic SQL is the only way. Db_Name() wont work as it always returns the then DB name in which it is run [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  10. RupertS New Member

    Adriaan - you're spot on. Will steer clear of INFORMATION_SCHEMA for now.

    Madhivanan,
    I'm afriad I disagree - run the following script!

    db_name returns the data base in which you call the stored proc from. (not the database stored proc is stored)

    Rupert



    use master
    go
    create proc sp_whatdb
    as
    select Db_Name()
    go
    sp_whatdb
    go
    use pubs
    go
    sp_whatdb
    go

    --Gives MASTER then PUBS
  11. Adriaan New Member

    Okay then, use:

    USE master
    GO

    CREATE dbo.so_MyProc()
    AS

    EXEC ('SELECT * FROM '+ RTRIM(DB_NAME()) + '.INFORMATION_SCHEMA.TABLES')

    GO
  12. DStevensTN New Member

    An FYI, SQL Server 2005 removes access to those system tables directly, it requires accessing the information through INFORMATION_SCHEMA
  13. Chappy New Member

    I dont agree that people should avoid the INFORMATION_SCHEMA views.
    The reason they often dont expose all the details tht the system tables do, is so that implementation can change under the scenes, and the views will not be affected.

    By all means use the system tables when the views arent good enough, but use the views in favour of the system tables, when the views are good enough. Youll save yourself lots of headaches in the future.
  14. Chappy New Member

    Infact, as DStevensTN points out, youll soon be left with no choice <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Sorry DS, I didnt see your reply at first
  15. Adriaan New Member

    Yikes! Well, as long as they don't drop functions like OBJECT_ID(), OBJECT_NAME(), OBJECTPROPERTY() and PERMISSIONS() I'm sure I can continue to avoid the useless format in which the INFORMATION_SCHEMA views expose vital information.
  16. satya Moderator

    As of now they are present in SQL 2005, so be cool [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  17. Adriaan New Member

    Mr Cool to you.[8D]
  18. FrankKalis Moderator

    With only some days till RTM date and the final RTM version available for download for MSDN subscribers, there's hardly a chance to drop yet another feature. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  19. satya Moderator

    You never know what will happen after final RTM, probably an early hotfix might do the job.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  20. FrankKalis Moderator

    Well...[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  21. Adriaan New Member

    But seriously ... why can't they do a proper job on those INFORMATION_SCHEMA views? The names are not terribly clear, there's loads of info in them that's basically redundant, they're not server-wide in case you need that, lots of information is presented row-by-row even where you could have columns on a single row ...
  22. FrankKalis Moderator

    There was and still is a lot of complaining about these views around. I've heard that they will be improved in the next version, but I have no idea if that is true, and, if so, why it took so long.

    As for names and stuff like that: These views are ANSI defined and part of the SQL 92 (I think) standard.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  23. Adriaan New Member

    Improvements - especially on the permissions side of things - would be most welcome ...
  24. FrankKalis Moderator

  25. satya Moderator

    Probably you could have it revised and suggested to MS, if you had time in beta testing participation. May try send your request tomailto: sqlwish@microsoft.com with the scenario.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  26. Adriaan New Member

    Our company is not forcing clients to upgrade their SQL Server installation. We will be adding SQL 2005 to our arsenal at some point - probably no sooner than when clients can no longer buy SQL 2000.

    It's good to know that system tables will no longer be accessible through queries, as that does affect certain dark corners of our software package. Thankfully it doesn't affect any core functionality (well, how could it?).
  27. Adriaan New Member

    Satya, thanks for the suggestion. We don't have any terribly fancy requirements - perhaps just to have the same sort of listings as you get in EM would be nice.

    Other than that, a PERMISSIONS() function where you specify the login (now only checks for current login) would be nice. Or indeed the possibility to use a non-fixed column identifier in SELECT statements, instead of using dynamic SQL. Or support of text columns in triggers. Or temp tables defaulting to the default collation of the database. (Let me stop myself here.)
  28. satya Moderator

    Adriaan
    Nice to hear your options and I'm not concerned on the makeup & cosmetic lookup of tools in SQL 2005, the more you participate in Beta testing would give better chance to improve the features or a workaround for your problems.

    I like the most of is the Management Studio which is far far bette than current Enterprise Manager and hope to avoid any flaky features in SQL 2005.

    Way to go....

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page