List the Databases that a User is the Schema Owner

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  March 8, 2011 at 10:55 am

  #246591

  Hello Everyone

  I am trying to code a script that will query and list all the databases that have a schema that is owned by a user, or all users.

  So if I have a userID, I would like to know all the schemas that are owned by that user, and the name of the database for each

  Can anyone help with this one. I am close, but still not finding what I need

  Thanks in advance

  Andrew SQLDBA

• Michael L John

  One Orange Chip

  Points: 27056

  More actions

  March 8, 2011 at 11:13 am

  #1296029

  How about:

  CREATE TABLE Junk.dbo.SchemaTest

  (

  Database_Name varchar(200),

  Schema_name varchar(200),

  Schema_Owner varchar(300)

  )

  sp_msforeachdb "USE [?]; INSERT INTO Junk.dbo.SchemaTest SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER FROM INFORMATION_SCHEMA.SCHEMATA"

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/