How To - Get the Database Schema using SQL

In This Topic

To get the current schema from the database use the following SQL:

Select all tables from Schema	Copy Code
select * from [database schema] where typeid = 1

This will return all the current tables in the active database.

You CANNOT self join to this table. It does not accept that behavior and will not return correct results. You must execute it in two statements rather than a single statement in a self join.

Check Schema in For Items	Copy Code
DECLARE @fkref as NCHAR(50); SET @fkref = SELECT objectId from [database schema] WHERE typeid = 1 and name = 'FileLinks'; SELECT EXISTS( SELECT name from [database schema] WHERE typeid = 3 and name = 'FileLinkId' and foreignReference = @fkref );

Check Schema in For Items

Copy Code

DECLARE @fkref as NCHAR(50);

SET @fkref = SELECT objectId from [database schema] WHERE  typeid = 1 and name = 'FileLinks';

SELECT EXISTS( SELECT name from [database schema] WHERE  typeid = 3 and name = 'FileLinkId'
   and foreignReference = @fkref );

TypeID enumerator meanings

Just performing a select * on the database schema will return all of the objects in the database. The following table represents the meaning of the typeid enumeration.

TypeID	Internal Type	Description
1	Table	Table entries
2	Index	Tables index structures
3	Column	Table column entries
4	Constraint	Table constraint entries
5	DefaultValue	Default value script
6	Identity	Identity Entry
7	Relationship	Foreign Key constraint
8	Trigger	Database trigger
9	Database Description	User entered description of database
10	View	View entry
11	CLR Stored proc	Entry point name of CLR Proc
12	Assembly	Assembly name included in database for CLR procedures
13	CLRTrigger	Entry point name of CLR Trigger
14	StoredProcedure	TSQL Stored procedure entry
15	UDF Entry	User defined functions written in TSQL

You cannot self reference or performs joins against the database schema object. It is not built for that type of operation. If you need to get schema out of a database in ADO.NET the best practice is to use the GetSchema call on the connection.

Use Try Catch Blocks to test for schema

Use a TRY CATCH block to test for a column that you need to add and handle the exception. This syntax also works in SQL Server as well.

Try / Catch Schema Change	Copy Code
BEGIN TRY select StorageTypeId from storage; END TRY BEGIN CATCH PRINT 'Column does not exist'; ALTER TABLE Storage ADD StorageTypeId Int; END CATCH

This same code could be written as below, but the language complexity is quite a bit higher and prone to error.

Alternate Test prior to Schema Change	Copy Code
-- Declare the variable DECLARE @tablepresent as bit; /* Add StorageTypeId to the Storage table. */ SET @tablepresent = SELECT EXISTS(SELECT name FROM [database schema] WHERE typeid = 3 AND name = 'StorageTypeId' AND foreignReference = (SELECT objectId FROM [database schema] WHERE typeid = 1 AND name = 'Storage')); IF @tablepresent = 1 BEGIN PRINT 'COLUMN ALREADY EXISTS - SKIPPING'; END ELSE BEGIN PRINT 'ADDING COLUMN TO Storage Table'; ALTER TABLE Storage ADD StorageTypeId Int; END

Alternate Test prior to Schema Change

Copy Code

-- Declare the variable
DECLARE @tablepresent as bit;

/* Add StorageTypeId to the Storage table. */
SET @tablepresent = SELECT EXISTS(SELECT name FROM [database schema]
  WHERE  typeid = 3 AND name = 'StorageTypeId' AND foreignReference =
  (SELECT objectId FROM [database schema] WHERE  typeid = 1 AND name = 'Storage'));

IF @tablepresent = 1
  BEGIN
   PRINT 'COLUMN ALREADY EXISTS - SKIPPING';
  END
ELSE
  BEGIN
   PRINT 'ADDING COLUMN TO Storage Table';
   ALTER TABLE Storage ADD StorageTypeId Int;
  END

SQL

SQL Reference - Database Schema

Send Feedback