Script to find specific schema_owner for all databases

Question

Post reply

Script to find specific schema_owner for all databases

ERIC CRUDELI

SSCommitted

Points: 1786
More actions
April 23, 2013 at 1:48 am

#273193

Hello,
Do you know a script to find schema_owner for all databases ?
I have this script but I can use it only to get information for only one database
select * from information_schema.schemata
where schema_owner <> 'dbo'
Thanks,
Eric

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 1

Just some quick answers:

EXEC sp_MSforeachdb

@command1='use [?]; if db_name() not in (''master'', ''model'', ''tempdb'', ''msdb'', ''distribution'') select * from information_schema.schemata'

go

Bazinga!

ScottPletcher SSC Guru Points: 101121 More actions · Answer 2

Here's something more detailed. Btw, I avoided using I_S.SCHEMATA for the schema names because of the associated warnings in Books Online -- it's best to avoid using I_S views at all in SQL Server.

IF OBJECT_ID('tempdb..#schemata') IS NOT NULL

DROP TABLE #schemata

CREATE TABLE #schemata (

CATALOG_NAME sysname NOT NULL,

SCHEMA_NAME nvarchar(128) NULL,

SCHEMA_OWNER nvarchar(128) NULL,

DEFAULT_CHARACTER_SET_CATALOG varchar(6) NULL,

DEFAULT_CHARACTER_SET_SCHEMA varchar(3) NULL,

DEFAULT_CHARACTER_SET_NAME sysname NULL

)

EXEC sp_MSforeachdb N'

IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')

RETURN

INSERT INTO #schemata

SELECT

N''?'', s.name, USER_NAME(s.principal_id), NULL, NULL,

(SELECT DEFAULT_CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.SCHEMATA is_s WHERE is_s.SCHEMA_NAME = s.name)

FROM [?].sys.schemas s

WHERE

s.principal_id <> DATABASE_PRINCIPAL_ID(N''dbo'') AND

s.principal_id <> DATABASE_PRINCIPAL_ID(N''guest'') AND

s.principal_id <> DATABASE_PRINCIPAL_ID(N''INFORMATION_SCHEMA'') AND

s.principal_id <> DATABASE_PRINCIPAL_ID(N''sys'') AND

s.principal_id < 16000

'

SELECT *

FROM #schemata

ORDER BY

CATALOG_NAME, SCHEMA_NAME

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 3

Thanks guy you're fantastic.

Just a question I have some databases where the owner is " NULL" or " UNKNOW " :hehe:. Could I have some troubles with SQL Server and which is the process to generate a owner " NULL" or " UNKNOW " ?

I have idea with "Unknow". May be when you have domain\user is desactivated or deleted from Active Directory but I'm not sure :satisfied:

Thanks,

Eric

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 4

Hello,

When I execute your script the result is wrong on schema_owner ?

IF OBJECT_ID('tempdb..#schemata') IS NOT NULL

DROP TABLE #schemata

CREATE TABLE #schemata (

CATALOG_NAME sysname NOT NULL,

SCHEMA_NAME nvarchar(128) NULL,

SCHEMA_OWNER nvarchar(128) NULL,

DEFAULT_CHARACTER_SET_CATALOG varchar(6) NULL,

DEFAULT_CHARACTER_SET_SCHEMA varchar(3) NULL,

DEFAULT_CHARACTER_SET_NAME sysname NULL

)

EXEC sp_MSforeachdb N'

IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')

RETURN

INSERT INTO #schemata

SELECT

N''?'', s.name, USER_NAME(s.principal_id), NULL, NULL,

(SELECT DEFAULT_CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.SCHEMATA is_s WHERE is_s.SCHEMA_NAME = s.name)

FROM [?].sys.schemas s

WHERE

s.principal_id <> DATABASE_PRINCIPAL_ID(N''dbo'') AND

s.principal_id <> DATABASE_PRINCIPAL_ID(N''guest'') AND

s.principal_id <> DATABASE_PRINCIPAL_ID(N''INFORMATION_SCHEMA'') AND

s.principal_id <> DATABASE_PRINCIPAL_ID(N''sys'') AND

s.principal_id < 16000

'

SELECT *

FROM #schemata

ORDER BY

CATALOG_NAME, SCHEMA_NAME

Example for THERIAQUE SCHEMA_OWNER is BAK but the script return three lines (normally only one) and SCHEMA_OWNER is wrong

CATALOG_NAME SCHEMA_NAME SCHEMA_OWNER

THERIAQUEdb_backupoperator NULL

THERIAQUEdb_owner NULL

THERIAQUEtheriaque NULL

Any idea :hehe:

ScottPletcher SSC Guru Points: 101121 More actions · Answer 5

Sorry, forgot I ended up using a database-specific function in the code (was trying to avoid it). This approach should be safer overall anyway: