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