SQL Server INFORMATION_SCHEMA contents?

Don't think this is possible The definition of the INFORMATION_SCHEMA. TABLES view is CREATE VIEW INFORMATION_SCHEMA. TABLES AS SELECT DB_NAME() AS TABLE_CATALOG, s.Name AS TABLE_SCHEMA, o.

Name AS TABLE_NAME, CASE o. Type WHEN 'U' THEN 'BASE TABLE' WHEN 'V' THEN 'VIEW' END AS TABLE_TYPE FROM sys. Objects o LEFT JOIN sys.

Schemas s ON s. Schema_id = o. Schema_id WHERE o.

Type IN ('U', 'V') so it pulls its information from sys. Objects however this in turn contains nothing about the INFORMATION_SCHEMA objects The metadata for these is accessed via sys. System_objects instead.

Don't think this is possible. The definition of the INFORMATION_SCHEMA. TABLES view is CREATE VIEW INFORMATION_SCHEMA.

TABLES AS SELECT DB_NAME() AS TABLE_CATALOG, s. Name AS TABLE_SCHEMA, o. Name AS TABLE_NAME, CASE o.

Type WHEN 'U' THEN 'BASE TABLE' WHEN 'V' THEN 'VIEW' END AS TABLE_TYPE FROM sys. Objects o LEFT JOIN sys. Schemas s ON s.

Schema_id = o. Schema_id WHERE o. Type IN ('U', 'V') so it pulls its information from sys.

Objects however this in turn contains nothing about the INFORMATION_SCHEMA objects. The metadata for these is accessed via sys. System_objects instead.

Yes, I've seen this. The sys schema is self-contained in the way I expected it. But I prefer using the more standardised information_schema, if possible.

– Lukas Eder Apr 22 at 10:17 @Lukas - Well I doubt that it will be possible because it isn't excluding these on the grounds of permissions or something it just simply isn't in the source data for the view. – Martin Smith Apr 22 at 10:20 Too bad... Thanks for the info! – Lukas Eder Apr 22 at 10:30.

You can use sys. All_views select SCHEMA_NAME(schema_id), name from sys. All_views order by 1,2.

1 Thanks. That would work as a workaround. But I'd really prefer to use the information_schema as that is more standardised across RDBMS – Lukas Eder Apr 22 at 10:33 For suitably small values of "standardized".

Doesn't run, or runs and returns no rows on every dbms here--SQL Server, Oracle, and PostgreSQL to name the three most popular. – Catcall Apr 22 at 11:12 @Catcall, you mean information_schema is not reliable on SQL Server? I'd be surprised... I agree Oracle doesn't have it, but Postgres does.So does HSQLDB (the most standard-adherent, I think), H2, MySQL (OK, quite a liberal interpretation of the standard) – Lukas Eder Apr 22 at 11:25 All of them have some INFORMATION_SCHEMA views, but none of them return anything useful for the OP's query.

PostgreSQL has the view, for example, but has no rows WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'. – Catcall Apr 22 at 11:36 1 I'm the OP :-) OK, you're right. But this works in Postgres 9.0: select * from information_schema.

Tables where table_schema = 'information_schema' (not capital letters...) – Lukas Eder Apr 22 at 16:47.

USE information_schema; SHOW TABLES; USE mysql; SHOW TABLES.

– abatishchev Apr 22 at 10:07 Thanks, this isn't about MySQL (which has a self-contained information_schema)... With SQL Server, the USE command is not for selecting schemata, but for selecting databases – Lukas Eder Apr 22 at 10:13.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.