-1

How do I determine the schema name for a given database "MyDB", listing all tables contained in the database (in MS SQL Express)? Is it "MyDB.Security.Schemas/INFORMATION_SCHEMA"?

I am using EF Core with Blazor, code first.

Background: I want to determine whether a certain table is present in the database.

Razzupaltuff
  • 2,250
  • 2
  • 21
  • 37
  • Do you want the "default" schema (right after you connect to the database) or the "current" schema (if you switched to another one)? – The Impaler Feb 10 '23 at 14:06
  • to check for tables you can use select * From sys.objects so where name = '' For schemas, one database can have many schemas. You can see a list of those in select * FROM sys.schemas – siggemannen Feb 10 '23 at 17:30

1 Answers1

0

You can select all the tables with a given and known schema from one database with this query:

SELECT  [schemas].[schema_id] AS SchemaId,
        [schemas].[name] AS SchemaName, 
        [tables].[name] AS TableName
FROM sys.schemas AS [schemas]
INNER JOIN sys.tables AS [tables]
    ON [schemas].[schema_id] = [tables].[schema_id]
WHERE [schemas].[name] = 'your-schema-name'

If you like to select over all databases on the server you can have a look at this SO answer: How do I list all tables in all databases in SQL Server in a single result set?

Sebastian S.
  • 1,173
  • 3
  • 13
  • 22