envschema

Snippet Name: envschema

Tags: helpful,scriptgeneration

Created Date: Oct 1 2021 8:05AM
Last Modified Date: Apr 1 2022 11:23AM

–desc: scripts all schemas for reference

Dependencies: none-standalone code

default description

Sample Output:

 
 {C}
 
 --desc: scripts all schemas for reference
 
   DECLARE @vbCrLf varchar(2); 
 
   SET @vbCrLf = CHAR(13) + CHAR(10);
 
   DECLARE @Results TABLE(ResultsID int IDENTITY(1,1) NOT NULL PRIMARY KEY,SchemaName VARCHAR(128),ResultsText varchar(8000) )
 
   INSERT INTO @Results(SchemaName,ResultsText)
 
   SELECT QUOTENAME(name) AS SchemaName,
 
     '--Schema ' + QUOTENAME(name) + @vbCrLf
 
     +  'IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = ''' + name + ''')' + @vbCrLf
 
     +  '  BEGIN' + @vbCrLf
 
     +  '    DECLARE @cmd varchar(500) = ''CREATE SCHEMA ' + QUOTENAME(name) + '''' + @vbCrLf
 
     +  '    --PRINT @cmd' + @vbCrLf
 
     +  '    EXEC (@cmd)' + @vbCrLf
 
     +  '  END ;' + @vbCrLf
 
   from sys.schemas 
 
   WHERE schema_id > 4 
 
   AND schema_id < 16384;
 
  SELECT DISTINCT SchemaName,ResultsText AS ResultsText FROM @Results