Sometimes we need to check the existence of a table in a script or scheduled task to record error logs, first executions etc ...
Let us take an example, a package of integration services that normally 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, as the first to run on the package and then continue. Being purists this just is not quite careful
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)If we change the previous instruction 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); GOIn this way we are doing careful and implementation will always be correct (unless the lack of permissions.)
Actually it has other applications because it could make any kind of script conditioned by the existence or not of objects in the DB or modification. For example, one could do the same query, or universal script to update / rebuild indexes based on elapsed time since last modification. We could control at administration level what is "inventing" that user with more permissions than it should have etc etc ...
Some more examples in msdn.
