March 25, 2012 at 11:58 am
Below is quote from 'Microsoft SQL Server 2012 Pocket Consultant':
Do not use INFORMATION_SCHEMA views to determine the schema of an object. Instead, query the sys.objects catalog view to determine the schema of an object.
Can somebody confirm it is true and explain/point me to source why is it recommended? I always use INFORMATION_SCHEMA, except where I can't.
Many thanks
March 25, 2012 at 12:29 pm
Yes, it is true. In fact, that's a direct copy from Books Online:
TABLE_SCHEMA
nvarchar(128)
Name of schema that contains the table.
Important:
Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.
I can't remember the exact reason, but if I recall there are some cases where the InformationSchema views won't show an object or won't show its schema. Can't remember when or why.
Information Schema stuff is ANSI standard, anything that's not ansi standard will not appear in those views. I'd personally only use them if I needed queries that worked across any ANSI-compatible database engine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2012 at 3:42 pm
Thanks for response. I have to look into these 'special cases'.
April 3, 2012 at 9:36 am
I believe this wording first showed up in the 2005 version of SQL Server. It may have to do with how the permissions work with respect to the schema securables.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy