Create table using the data dictionary on SQL Server

Sometimes we need to check the existence of a table in a script or scheduled task to record events of errors, first executions etc ... Let us take an example, a package of integration services that we tend to distribute or run there where we go and that leaves traces in a custom table that is not the default for loading logs.

We could always include a performance task or sql script, run right or wrong, is the first to run on the package and then continue. Being purists this just is not quite "neat"

 CREATE TABLE LogsEtl
(Execution int PRIMARY KEY,
Package varchar (50),
Date datetime);
GO 

In the first run the output is correct but fail after creating the table.This can substitute vision consulting sys.objects , where there is a record for each object in the database, and verify the existence of the table before creating it.

The visibility of the metadata is limited to securables owned by the user or on which the user has any permissions. The structure of the view is as follows:

 name (sysname)
object_id (int)
schema_id (int) parent_object_id (int) type (char (2)) type_desc (nvarchar (60)) create_date (datetime) modify_date (datetime) is_ms_shipped (bit) is_published (bit) is_schema_published (bit) 

If we change the previous ruling by a create table conditioned by a query on the view looking the table name as parameter object_id (function that returns the unique identifier of an object by name) have something like this:

 IF NOT EXISTS (SELECT * FROM WHERE object_id = OBJECT_ID sys.objects (N '[dbo]. [LogsEtl]') AND TYPE = N'U ')
CREATE TABLE LogsEtl
(Execution int PRIMARY KEY,
Package varchar (50),
Date datetime);
GO

In this way we are doing "neat" and implementation will always be correct (unless the lack of permits.) Actually it has other applications because it could make any kind of script conditioned by the existence of objects in the DB or modification.

For example, one could do the same query, or universal script to update / rebuild indexes based on time spent with since last modification. We could control the level of administration that is "inventing" that user with more etc etc ...in msdn are some examples.