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

SQL Server --- Query that will display the columns in the table along with their data-types and their position in the table they are in..

Sys tables are the source of information for any metadata related queries. This stored procedure will take table name and schema name as an input and will display all the columns, their data types and their position in the table. Example run is for the Adventureworks database, on HumanResources.Employee table.

CREATE PROC getAllColumns(@tablename VARCHAR(130),@schemaname VARCHAR(130)) AS

BEGIN 

select DISTINCT SC.name [Columnname],STS.name [Datatype],SC.column_id [Position]

from sys.columns SC

INNER JOIN sys.types STS

ON SC.user_type_id=STS.user_type_id

INNER JOIN sys.tables ST

ON SC.object_id = ST.object_id

WHERE ST.name = @tablename AND ST.schema_id = SCHEMA_ID(@schemaname)

ORDER BY SC.column_id

END

EXEC getAllColumns 'Employee','HumanResources'

SQL Server -- Query to see all the tables in a given schema

Information about the tables can be found from sys tables. Here is the stored procedure to find all the tables for a given schema. Input to the procedure is schema name. Example is the sample run of this procedure on Adventureworks database.

CREATE PROC getAllTables(@schemaname VARCHAR(130)) AS

SELECT name AS 'tableName'

FROM sys.tables ST

WHERE SCHEMA_ID(@schemaname) = ST.schema_id

EXEC getAllTables 'HumanResources'