1

I have three schemas in my SQL Server database, but when I query over INFORMATION_SCHEMA.TABLES only tables that belong to two of the schemas are listed. Tables in the third schema are not listed. What can be the cause?

My query is:

USE MyDatabase
SELECT * FROM INFORMATION_SCHEMA.TABLES;

I think it is a permissions issue, but I can't find it. I have compared the schemas (and their owner roles) and their permissions look the same. I can't find any difference between them.

I want to list all the tables, not schemata. Even when I run SELECT * FROM INFORMATION_SCHEMA.SCHEMATA my third schema is not in the result.

When I run SELECT OBJECT_ID('Schema3.Table1') I receive NULL whereas the result for SELECT OBJECT_ID(Schema2.Table1)is the object_id.

4
  • Does the third schema show up when querying sys.objects? From this link: - msdn.microsoft.com/en-us/library/ms186224.aspx - ** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of an object is to query the sys.objects catalog view. INFORMATION_SCHEMA views could be incomplete since they are not updated for all new features. Mar 2, 2017 at 10:13
  • A likely cause is permissions. You must have been granted some permission on an object for meta-data to be visible. See msdn.microsoft.com/en-us/library/ms187113.aspx.
    – Dan Guzman
    Mar 2, 2017 at 11:29
  • What happens if you run select s.name from sys.schemas s; ? Do you see three schemas? Also try, select s.name, count(t.object_id) from sys.schemas s left join sys.tables t on s.schema_id = t.schema_id group by s.name; which will give you the count of tables for each schema.
    – Hannah Vernon
    Mar 3, 2017 at 21:52
  • 1
    @MaxVernon Both your queries works as it should be, the first one shows the third schema as well as other two, and when I run second query number of tables in third schema is correct. I don't know what is the reason that INFORMATION_SCHEMA does not show the third schema. But I can change my query that it uses sys schema instead.
    – Ehsan
    Mar 6, 2017 at 9:57

1 Answer 1

1

Aaron Bertrand has an excellent post here about why you shouldn't trust the INFORMATION_SCHEMA views - unreliable information_schema schema information on SQL Server?

To summerize:

1). The wording in the documentation is not accurate, and it is in the process of being corrected (see Connect #686118). I'm not sure if they will correct the 2005, 2008 and 2008 R2 docs all at once, or whether the older versions will even get updated. The point is that I can not envision a case where the schema in either view is incorrect, but even more so, that info_schema is incorrect while sys.objects is correct. The latter is impossible - the info_schema view is based entirely on the sys.objects view (just look at SELECT OBJECT_DEFINITION (OBJECT_ID ('INFORMATION_SCHEMA.TABLES'));), so if one is incorrect, they're both incorrect. There are probably obscure cases where they can both be incorrect, but not in current versions (for example, in SQL Server 2000, with the config option allow updates enabled, delete from sysusers a user who owns an object - not really relevant or possible today, and not something I'm willing to try, but it's the only one I can imagine would have motivated the current wording at any point in time).

2). In general, INFORMATION_SCHEMA views should be avoided in favor of the catalog views introduced in SQL Server 2005 (and augmented since then). Why? Because the catalog views continue being developed as new features are added to SQL Server, while the info_schema views have not. As I mentioned in my comment, try to find information about filtered indexes in info_schema. Same goes for included columns, XML indexes, identity/computed columns, foreign keys against unique indexes - these are all either missing entirely or represented differently in the info_schema views. In Denali they added an info_schema view for Sequences but again this meets the bare minimum of the standard and doesn't include any information about SQL Server-specific implementation details (for example, whether it's exhausted, and if they add any new features in the future you can be sure the info_schema view will not be kept in the loop). The only case where you would stick to info_schema views is if (a) you are writing metadata routines that need to work across info_schema-compliant platforms AND (b) you aren't using any platform-specific features that will be missed. Aside from multi-platform vendor tools this is probably a pretty rare scenario (and even in that case may lead to displeased customers who are using those features and the tool didn't pick them up).

3). I filed a separate Connect suggestion (Connect #686121) that they plaster a warning about this incompleteness on all INFORMATION_SCHEMA view topics in Books Online. I don't think it's very well known that they are not the preferred way to get metadata out of SQL Server, and who could blame folks for not seeing this - after all, we're always told that using standards-compliant methods is a "best practice" and using proprietary methods are the opposite. As with a lot of database things, "it depends" - but I suspect, more often than not, you're better off using the sys catalog views unless you're in that rare scenario where you're using only the features in SQL Server that are common to the standard. I don't think I've come across a single instance in any capacity where this was the case, but I'm more than happy to learn of them if they do exist.

1
  • Thank you Scott, your answer and the link was really helpful, even in my case it should be some permission related issue. When I run SELECT OBJECT_ID('Schema3.Table1') I receive NULLwhereease the result for SELECT OBJECT_ID(Schema2.Table1)is the object_id.
    – Ehsan
    Mar 3, 2017 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.