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