How to get list of user schemas?

• j_maloney

  Right there with Babe

  Points: 731

  More actions

  June 17, 2011 at 3:48 pm

  #242343

  Hi all, I'm trying to return a concatenated list of user-defined schemas plus dbo. With no where clause filtering I get a slew of built-in schemas like 'db_accessadmin' and 'sb_backupoperator' which I don't need. I noticed most of these built-in schemas have a schemaID > 16000, but even when using this in my where clause, I still get 'guest', 'INFORMATION_SCHEMA', and 'sys'. Is there a better method than adding 'AND NOT IN ()'?

  -- =============================================

  -- Description:Return concatenated string of schemas for a given DB

  -- Example:

  --EXEC util.GetSchemas 'JM'

  -- Note: Filtering out built-in role schemas (WHERE Schema_ID < 16000)

  -- =============================================

  ALTER PROCEDURE [util].[GetSchemas]

  @UseDB VARCHAR(100),

  @OutPut VARCHAR(MAX)= NULL OUTPUT

  AS

  BEGIN

  SET NOCOUNT ON;

  SET STATISTICS IO OFF

  DECLARE @sql NVARCHAR(MAX)--for dynamic sql

  --validate @Use DB if you wish

  /** **/

  SET @sql = ISNULL(@sql,'')

  +

  'SELECT

  STUFF((

  SELECT '

  +

  '''' + ',' + '''' + ' + ' + '''''' + ''''''

  + ' + [name] + ' + ''''''+ ''''''

  + '

  FROM ' + @UseDB + '.sys.schemas S

  WHERE Schema_ID < 16000

  ORDER BY [name]

  FOR

  XML PATH(' + '''' + '''' + ')

  ) , 1 , 1 , ' + '''' + '''' + ') As SchemaList'

  --PRINT @sql--debug

  DECLARE @Table TABLE (List VARCHAR(MAX))

  INSERT INTO @Table

  EXEC sp_executesql @sql

  --SELECT * FROM @TABLE

  SET @OutPut = (SELECT TOP 1 List FROM @TABLE)

  END

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  June 17, 2011 at 5:01 pm

  #1340737

  CREATE PROCEDURE GetSchemasMM

  @UseDb [sysname],

  @Output [varchar](MAX) OUTPUT

  AS

  SET NOCOUNT ON;

  DECLARE @sql [nvarchar](MAX);

  SELECT @sql = N'SELECT @result=STUFF((SELECT '',''+[SCHEMA_NAME] FROM ['+@UseDb+N'].[INFORMATION_SCHEMA].[SCHEMATA] WHERE [SCHEMA_OWNER] = ''dbo'' FOR xml PATH('''')),1,1,'''')'

  EXEC sp_executesql @sql,N'@result [varchar](MAX) OUTPUT',@result = @Output OUTPUT

  GO

  DECLARE @Output [varchar](MAX)

  EXEC GetSchemasMM 'AdventureWorks',@Output OUTPUT

  SELECT @Output

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]