SQL Server -- Query to see all the schemas given a database name..

This stored procedure gives all the schemas within a given database. It gives all the system schemas also. If you want to display only the schemas that have associated tables with them, you can join it with sys.tables and select only those schemas. Note that I have done error checking to make sure that the database exists in the system.

CREATE proc [dbo].[getSchemaInfo](@DatabaseName VARCHAR(100)) AS

BEGIN

      DECLARE @database_id INT

      SET @database_id = -1

      select @database_id = database_id from sys.databases

      where name = @DatabaseName

      if(@database_id = -1)

      BEGIN

            RAISERROR('Incorrect database name',1,1)

      END

      ELSE

      BEGIN

            DECLARE @SQL NVARCHAR(2000)

            SET @SQL = 'Use '+@DatabaseName+'; '+

                           'SELECT name AS [Schema Name] '+

                           'FROM sys.schemas;';

            EXEC sp_executesql @SQL

      END  

END