-1

I'm looking at a long list of subdivisions below a server. I know what all of them are except the ????. First comes the server name/alias, then DB name... Then under that DB there's multiple ?????'s, and under each one of them multiple tables.

So what type of "object" is the ???? in

From [ServerName].DatabaseName.????Name.TableName. What are the question marks?

And what SQL query can I use to get a list of all ????? in a specific Server and Database?

Giganoob
  • 37
  • 5
  • 4
    Server.DataBase.Schema.Table – Sergey Sep 30 '22 at 07:28
  • Thanks a lot @Sergey! Do you also know what query I could use to get all schemas in specific Server 'X', Database 'Y'? – Giganoob Sep 30 '22 at 07:32
  • 1
    I guess, the simplest way is to google something like "Get all schemas in db", e.g. I found this query SELECT s.name AS schema_name, u.name AS schema_owner FROM sys.schemas s INNER JOIN sys.sysusers u ON u.uid = s.principal_id ORDER BY s.name; – Sergey Sep 30 '22 at 07:39
  • @Sergey Thanks, I also found that and tried it. The problem is that in that query you are not specifying name of database or server anywhere. So it will just return the schema of the DB at the "top of the tree". Still don't know the syntax if I want to drill down to a specific server and a specific DB. I tried SELECT sys.schemas.* FROM [ServerName].DatabaseName.sys.schemas (using the correct server/DB names of course) but for some reason it doesn't work. – Giganoob Sep 30 '22 at 07:43
  • 1
    not sure about specific server (you need to dive into linked-server's world). Regarding databases and schemas please take a look https://dataedo.com/kb/query/sql-server/list-schemas-in-all-databases – Sergey Sep 30 '22 at 07:47
  • Thanks @Sergey, found what I was looking for after refining my google search to include the phrase linked servers. "get schema from linked server". – Giganoob Sep 30 '22 at 07:56

1 Answers1

0

The answer for the 2nd part of my question (list of schemas in specific server/db) was this:

SELECT s.name AS schema_name
      ,t.name AS table_Name
      ,c.name AS column_Name
    --,c.max_length
FROM [SERVER].[DB].sys.tables t
JOIN [SERVER].[DB].sys.schemas s ON t.schema_id = s.schema_id
JOIN [SERVER].[DB].sys.columns c ON t.object_id = c.object_id
--WHERE s.name = 'dbo'
ORDER BY s.name, t.name, c.name

Taken from https://stackoverflow.com/a/48173311/3365188

Giganoob
  • 37
  • 5
  • This won't tell you about schemas that don't have a table on them; which can easily exist. – Thom A Sep 30 '22 at 08:05
  • Thanks for the clarification. Fortunately I only care about schemas that have tables so the above should suffice. – Giganoob Sep 30 '22 at 08:15