SQL Reference - Database Schema

In This Topic

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

SELECT * FROM [database schema] WHERE typeid = 1

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

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

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 Example	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 Schema Test Example	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 Schema Test Example

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

Remarks

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.

Example

You can use multiple statements to achieve the same results as self referencing query, but we don't recommend this as a best practice. It is included here for demo purposes only.

Multi-statement Example	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 );

Multi-statement Example

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 );