I'm looking for a SQL statement to get ALL tables in the particular database - including INFORMATIONAL_SCHEMA and sys.

Something like SQLTables( NULL, NULL, NULL ); does - all catalogs with all schemas with all tables.

All answers I see here are returning only user created tables or even the subsets of it.

CodePudding user response：

If you want all the user tables, user views, and system views, then

select schema_name(schema_id) schema_name, name object_name, type, type_desc 
from sys.system_views
union all 
select schema_name(schema_id) schema_name, name object_name, type, type_desc 
from sys.tables 
union all 
select schema_name(schema_id) schema_name, name object_name, type, type_desc 
from sys.views

This excludes system base tables, which are tables, but you can't SELECT from them unless you're connected to the Dedicated Administrator Connection.

If you want those too, add

select schema_name(schema_id) schema_name, name object_name, type, type_desc 
from sys.objects 
where type = 'S'

If you just need a count of the objects, then you can push the UNION ALL into a subquery like this:

select count(*) table_count
from (
    select schema_name(schema_id) schema_name, name object_name, type, type_desc 
    from sys.system_views
    union all 
    select schema_name(schema_id) schema_name, name object_name, type, type_desc 
    from sys.tables 
    union all 
    select schema_name(schema_id) schema_name, name object_name, type, type_desc 
    from sys.views
) d

CodePudding user response：

There is a system view for that also, it's called sys.all_objects

select
  o.name,
  s.name schema_name,
  o.type,
  o.type_desc
from sys.all_objects o
join sys.schemas s on s.schema_id = o.schema_id
where o.type IN ('U', 'V', 'IT', 'S');

db<>fiddle

You should generally avoid INFORMATION_SCHEMA it is for compatibility only.