LLI Wiki — My Digital Knowledge Base

SQL Server : lister les schémas et leur propriétaire dans toutes les bases

Lister les schémas de toutes les bases avec leur propriétaire, et les objets d'un schéma.

Lister les schémas de toutes les bases d'une instance avec leur propriétaire, en version légère ou complète, et lister les objets d'un schéma.

Version légère : schémas de toutes les bases

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((SELECT ' UNION ALL
SELECT ' +  + QUOTENAME(name,'''') + ' as DbName, cast(Name as varchar(128)) COLLATE DATABASE_DEFAULT 
AS Schema_Name FROM ' + QUOTENAME(name) + '.sys.schemas'

FROM sys.databases Order BY [name] FOR XML PATH(''),type).value('.','nvarchar(max)'),1,12,'')

SET @SQL = @SQL + ' ORDER BY DbName, Schema_Name'

EXECUTE (@SQL)

Version complète : schémas et propriétaire

Seuls les schémas dont le propriétaire est un utilisateur de base (et non sys, guest ou INFORMATION_SCHEMA) sont listés, pour les bases utilisateur.

declare @query nvarchar(max)

set @query = 
(select 'select ''' + name + ''' as database_name,
              s.Name COLLATE DATABASE_DEFAULT as schema_name,
              u.name COLLATE DATABASE_DEFAULT as schema_owner 
        FROM ['+ name + '].sys.schemas s
        JOIN ['+ name + '].sys.sysusers u on u.uid = s.principal_id
        where u.issqluser = 1
              and u.name not in (''sys'', ''guest'', ''INFORMATION_SCHEMA'')
        union all '
    from sys.databases 
    where database_id > 4
    for xml path(''), type).value('.', 'nvarchar(max)')

set @query = left(@query,len(@query)-10) 
                        + ' order by database_name, schema_name'

execute (@query)

Lister les objets d'un schéma

Cette requête lit la base courante uniquement.

SELECT *
FROM sys.objects WHERE schema_id = SCHEMA_ID('dbo')

Remarques

Mastodon LLIMastodon SocialFosstodon