none
VIEW ALL SCHEMA IN ALL DATABASE RRS feed

  • Question

  • Please can someone help me. I need to create a query that will show all the schema in all databases not just the schemas within a particular database. I have 6 databases that I want to check to see if a schema exsits called 'ETL' in all the databases. Is there a system table like in Oracle where you can see everything from a top level table.

     

     

    Wednesday, May 5, 2010 12:22 AM

Answers

  • SQL Server does not have any such metadata that describes all databases, each of the metadata tables is driven by the database id.  Because of this you will have to query each database.  To do this you will have to use a cursor or the undocumented command Naom posted. Personally, I dont like relying on undocumented stored procedures in my code, especially when the underlying code is something as trival as a cursor.  IMO it is best to roll your own cursor to get the underlying information. 

    DECLARE @sql NVARCHAR(500),
    		@db NVARCHAR(255),
    		@Schema NVARCHAR(255)
    		
    SET @Schema = 'dbo'
    
    DECLARE curGetSchema CURSOR FAST_FORWARD FOR
    SELECT d.name
    FROM sys.databases d
    WHERE [database_id] > 4 --no sys dbs
    
    OPEN curGetSchema
    FETCH NEXT FROM curGetSchema INTO @db
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	SET @sql = N'
    	Use ' + QUOTENAME(@db) + ';' + CHAR(13) + '
    	IF EXISTS(SELECT 1 FROM sys.schemas WHERE name = @dyn_Schema)
    	BEGIN
    	PRINT ' + QUOTENAME(@db,'''') + '
    	END'
    	
    	--PRINT @sql
    	EXEC sp_executesql @sql, N'@dyn_Schema NVARCHAR(255)', @dyn_Schema = @Schema
    
    	FETCH NEXT FROM curGetSchema INTO @db
    END
    
    CLOSE curGetSchema
    DEALLOCATE curGetSchema

    http://jahaines.blogspot.com/
    Wednesday, May 5, 2010 1:40 PM
    Moderator
  • As far as I know, you need to loop through all databases. You can do it using sys.databases or you can also use undocumented stored procedure sp_MSForEachDB

    Check Using sp_MSForEachDB

    For some reason sp_MSForEachDB didn't work for me in my test:

    EXEC sp_MSForEachDB 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
    begin
     if exists (select 1 from [?].information_schema.schemata where schema_name=''HumanResources'')
     print ''[?]''
    end' 

    Or

    declare @Sql nvarchar(max)
    create table AllDBSchemas ([DB Name] sysname, [Schema Name] sysname)
    
    select @Sql = coalesce(@Sql,'') + '
    insert into AllDBSchemas
    
    select ' + QUOTENAME(name,'''') + ' as [DB Name], [Name] as [Schema Name] from ' + 
    QUOTENAME(Name) + '.sys.schemas order by [DB Name],[Name];' from sys.databases
    order by name
    
    execute(@SQL)
    
    select * from AllDBSchemas order by [DB Name],[SCHEMA NAME] 
    See last problem in this blog 

    How to get information about all databases without a loop

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, May 5, 2010 12:43 AM
    Moderator
  • NP Naom,

    I cant say that most people should shrink log files because the log typically grows right back.  The important thing to rembember is how expensive it is to grow the log and that transactions can and will wait on the growth to occur.  This means you are typically stuck in an endless cycle of growing and shrinking, and really dont gain anything!!!!!  It should also be pointed out that changing the recovery model to simple trucates the log, which means you had better backup the database (full); otherwise, you could have severe data loss depending on when your last backup was.  A rule of thumb that I live by is.... One should only shrink the log file if the disk subsystem is really starved for storage and when necessary use dbcc shrinkfile (without changing the recovery model).  If your log is not shrinking in full or bulk logged recovery, it means you are probably not backing the database up as often as you need too.

    With all of that said, here is how I would change your code.

    DECLARE @sql NVARCHAR(500),
        @db NVARCHAR(255),
        @Log NVARCHAR(255)  
     
    DECLARE curShrinkDBs CURSOR FAST_FORWARD FOR
    SELECT d.name,mf.name
    FROM sys.databases d
    INNER JOIN sys.master_files mf ON [d].[database_id] = [mf].[database_id]
    WHERE 
    	d.[database_id] > 4 --no sys dbs
    	AND d.recovery_model = 1
    	AND d.is_read_only = 0
    	AND mf.[type] = 1 --log files
    ORDER BY d.name
     
    OPEN curShrinkDBs
    FETCH NEXT FROM curShrinkDBs INTO @db,@Log
     
    WHILE @@FETCH_STATUS = 0
    BEGIN
     
      SET @SQL = N'
      Use ' + QUOTENAME(@db) + ';' + CHAR(13) + '
      ALTER DATABASE ' + QUOTENAME(@db) + ' SET RECOVERY SIMPLE;
      DBCC SHRINKFILE (@dyn_Log, 1);
      ALTER DATABASE ' + QUOTENAME(@db) + ' SET RECOVERY FULL;'
     
    	PRINT @sql
      --EXEC SP_EXECUTESQL @sql, N'@dyn_Log NVARCHAR(255)', @dyn_Log = @Log
     
      FETCH NEXT FROM curShrinkDBs INTO @db,@Log
    END
     
    CLOSE curShrinkDBs
    DEALLOCATE curShrinkDBs

    http://jahaines.blogspot.com/
    Wednesday, May 5, 2010 4:55 PM
    Moderator

All replies

  • As far as I know, you need to loop through all databases. You can do it using sys.databases or you can also use undocumented stored procedure sp_MSForEachDB

    Check Using sp_MSForEachDB

    For some reason sp_MSForEachDB didn't work for me in my test:

    EXEC sp_MSForEachDB 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
    begin
     if exists (select 1 from [?].information_schema.schemata where schema_name=''HumanResources'')
     print ''[?]''
    end' 

    Or

    declare @Sql nvarchar(max)
    create table AllDBSchemas ([DB Name] sysname, [Schema Name] sysname)
    
    select @Sql = coalesce(@Sql,'') + '
    insert into AllDBSchemas
    
    select ' + QUOTENAME(name,'''') + ' as [DB Name], [Name] as [Schema Name] from ' + 
    QUOTENAME(Name) + '.sys.schemas order by [DB Name],[Name];' from sys.databases
    order by name
    
    execute(@SQL)
    
    select * from AllDBSchemas order by [DB Name],[SCHEMA NAME] 
    See last problem in this blog 

    How to get information about all databases without a loop

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, May 5, 2010 12:43 AM
    Moderator
  • Is there no simple way to do it. In Oracle there is a system table where you can view all schema, Does MS not have one.

    This seems a very complexed way to carry out a simple task.

     

     

     

     

    Wednesday, May 5, 2010 4:49 AM
  • This is the basic system view query:

    SELECT *
    FROM [AdventureWorks2008].[sys].[schemas]
    /*
    name
    ....
    HumanResources
    Person
    Production
    Purchasing
    Sales
    ....
    */

    You can use dynamic SQL to visit all databases.  Check out the sprocAllDBsSysobjectCounts stored procedure at:

    The Power & Flexibility of SQL Server Dynamic SQL http://www.sqlusa.com/bestpractices2005/quotename/


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Wednesday, May 5, 2010 5:05 AM
    Moderator
  • The problem is that the query below is only going to show me the schema objects within that database. Is there no central table in the MS Server that stores all database names and schema's. I will be really suprised if I can't carry out such a simple task.

    SELECT *
    FROM [AdventureWorks2008].[sys].[schemas]


    All I want is to create a query at System level including all databases that would perform something like below.

    SELECT Table_name, Schema_Name

    FROM All Databases

    I know this is not correct but this is all I need to do.

    Wednesday, May 5, 2010 5:25 AM
  • SELECT Table_name, Schema_Name

    FROM All Databases


    Can you really do that in ORACLE for 20 databases as an example?

    In SQL Server, there are the system databases (master, tempdb, model, msdb) you want to exclude. In addition, other application databases which other MS software controlling like Reporting Services ReportServer & ReportServerTempDB you need to exclude.

    So your simple idea just became quite complicated, hence the dynamic SQL stored procedure recommendation.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Wednesday, May 5, 2010 5:45 AM
    Moderator
  • SQL Server does not have any such metadata that describes all databases, each of the metadata tables is driven by the database id.  Because of this you will have to query each database.  To do this you will have to use a cursor or the undocumented command Naom posted. Personally, I dont like relying on undocumented stored procedures in my code, especially when the underlying code is something as trival as a cursor.  IMO it is best to roll your own cursor to get the underlying information. 

    DECLARE @sql NVARCHAR(500),
    		@db NVARCHAR(255),
    		@Schema NVARCHAR(255)
    		
    SET @Schema = 'dbo'
    
    DECLARE curGetSchema CURSOR FAST_FORWARD FOR
    SELECT d.name
    FROM sys.databases d
    WHERE [database_id] > 4 --no sys dbs
    
    OPEN curGetSchema
    FETCH NEXT FROM curGetSchema INTO @db
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	SET @sql = N'
    	Use ' + QUOTENAME(@db) + ';' + CHAR(13) + '
    	IF EXISTS(SELECT 1 FROM sys.schemas WHERE name = @dyn_Schema)
    	BEGIN
    	PRINT ' + QUOTENAME(@db,'''') + '
    	END'
    	
    	--PRINT @sql
    	EXEC sp_executesql @sql, N'@dyn_Schema NVARCHAR(255)', @dyn_Schema = @Schema
    
    	FETCH NEXT FROM curGetSchema INTO @db
    END
    
    CLOSE curGetSchema
    DEALLOCATE curGetSchema

    http://jahaines.blogspot.com/
    Wednesday, May 5, 2010 1:40 PM
    Moderator
  • NP Naom,

    I cant say that most people should shrink log files because the log typically grows right back.  The important thing to rembember is how expensive it is to grow the log and that transactions can and will wait on the growth to occur.  This means you are typically stuck in an endless cycle of growing and shrinking, and really dont gain anything!!!!!  It should also be pointed out that changing the recovery model to simple trucates the log, which means you had better backup the database (full); otherwise, you could have severe data loss depending on when your last backup was.  A rule of thumb that I live by is.... One should only shrink the log file if the disk subsystem is really starved for storage and when necessary use dbcc shrinkfile (without changing the recovery model).  If your log is not shrinking in full or bulk logged recovery, it means you are probably not backing the database up as often as you need too.

    With all of that said, here is how I would change your code.

    DECLARE @sql NVARCHAR(500),
        @db NVARCHAR(255),
        @Log NVARCHAR(255)  
     
    DECLARE curShrinkDBs CURSOR FAST_FORWARD FOR
    SELECT d.name,mf.name
    FROM sys.databases d
    INNER JOIN sys.master_files mf ON [d].[database_id] = [mf].[database_id]
    WHERE 
    	d.[database_id] > 4 --no sys dbs
    	AND d.recovery_model = 1
    	AND d.is_read_only = 0
    	AND mf.[type] = 1 --log files
    ORDER BY d.name
     
    OPEN curShrinkDBs
    FETCH NEXT FROM curShrinkDBs INTO @db,@Log
     
    WHILE @@FETCH_STATUS = 0
    BEGIN
     
      SET @SQL = N'
      Use ' + QUOTENAME(@db) + ';' + CHAR(13) + '
      ALTER DATABASE ' + QUOTENAME(@db) + ' SET RECOVERY SIMPLE;
      DBCC SHRINKFILE (@dyn_Log, 1);
      ALTER DATABASE ' + QUOTENAME(@db) + ' SET RECOVERY FULL;'
     
    	PRINT @sql
      --EXEC SP_EXECUTESQL @sql, N'@dyn_Log NVARCHAR(255)', @dyn_Log = @Log
     
      FETCH NEXT FROM curShrinkDBs INTO @db,@Log
    END
     
    CLOSE curShrinkDBs
    DEALLOCATE curShrinkDBs

    http://jahaines.blogspot.com/
    Wednesday, May 5, 2010 4:55 PM
    Moderator