Automate synonym creation

Let the code write the code.

We frequently want to access tables within another database on the same server. I often want to do this within a BI solution when the staging area is a separate database on the same server as the dimensional model database.

Never access these tables via 3-part names. If things change, say for example the staging database was moved to another server or to within the same database as the facts/dimensions you need to change your entire codebase where these tables are referenced.

My preferred solution here is to add synonyms as an abstraction layer. This is a very elegant solution, allowing you to move the base tables anywhere you like and for things to carry on working as before with a synonym change. An alternative here is to use views but synonyms are preferred for a number of reasons.

  1. Views become stale and have to be updated if the underlying table changes, synonyms do not.
  2. You can use hints on synonyms as if you were referencing the table itself, this is not supported with views.

I have automated the creation of such synonyms to produce a consistent repeatable structure for hundreds or even thousands of tables. Connect to the database containing the base tables to reference (the staging database in the example above) and run this script, this will produce statements to check for existence, drop, create synonym and also create an extended property to document who created the synonym and when. Take the output and run against your database that should reference the base tables (the dimensional model database in the example above).


/*
Name:

(C) Andy Jones

mailto:andrew@aejsoftware.co.uk

Description: -

Creates if exists / drop / create synonym statements for each table in a database.
Creates an extended property for each synonym with who created and when.

Change History: -
1.0 12/11/2015 Created.

License: free to download and use providing this header is kept in place. Author consent is required for
any sale or re-distibution.
*/
SET NOCOUNT ON;

WITH Command ( CommandID, Command )
AS ( SELECT 1 ,
'IF EXISTS (SELECT 1 FROM sys.synonyms WHERE [object_id] = OBJECT_ID(''_Replace_QualifiedName''))'
UNION ALL
SELECT 2 ,
CHAR(9) + 'BEGIN;'
UNION ALL
SELECT 3 ,
CHAR(9) + 'DROP SYNONYM _Replace_QualifiedName;'
UNION ALL
SELECT 4 ,
CHAR(9) + 'END;'
UNION ALL
SELECT 5 ,
CHAR(9) + ''
UNION ALL
SELECT 6 ,
'CREATE SYNONYM _Replace_QualifiedName FOR '
+ DB_NAME() + '._Replace_QualifiedName;'
UNION ALL
SELECT 9 ,
CHAR(9) + ''
UNION ALL
SELECT 10 ,
'EXEC sp_addextendedproperty @name = N''MS_Description'',@value = N'''
+ SYSTEM_USER + ' '
+ CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)
+ ': synonym pointing at ' + DB_NAME()
+ '._Replace_QualifiedName'',@level0type = ''SCHEMA'',@level0name = N''dbo'',@level1type = ''SYNONYM'',@level1name = N''_Replace_Name'', @level2type = NULL,@level2name = NULL;'
UNION ALL
SELECT 11 ,
CHAR(9) + ''
),
Obj ( QualifiedName, Name )
AS ( SELECT OBJECT_SCHEMA_NAME(o.object_id) + N'.' + o.name ,
o.name
FROM sys.objects AS o
WHERE o.type_desc IN ( N'USER_TABLE', N'VIEW' )
AND OBJECT_SCHEMA_NAME(o.[object_id]) <> N'tSQLt'
AND o.name NOT IN (N'sysdiagrams')
)
SELECT Command = REPLACE(REPLACE(c.Command, '_Replace_QualifiedName',
o.QualifiedName), '_Replace_Name',
o.Name)
FROM Command AS c
CROSS JOIN Obj AS o
ORDER BY o.QualifiedName ,
c.CommandID;

 

Count of rows in a table

Here are some methods to return the row count from a table: –

SELECT row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderHeader') AND
index_id < 2; --0 = Heap; 1 = Clustered index.
SELECT row_count = SUM(rows)
FROM sys.partitions
WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderHeader') AND
index_id < 2; --0 = Heap; 1 = Clustered index.
EXECUTE sys.sp_spaceused 'Sales.SalesOrderHeader';
EXECUTE dbo.sp_DataProfile 'Sales.SalesOrderHeader',0;
SELECT COUNT(*) FROM Sales.SalesOrderHeader;

The first three rely on the dynamic management views sys.dm_db_partition_stats and sys.partitions, where the latter two will actually scan the table or index to count the rows.

sp_DataProfile is a great free (see EULA for terms) tool, to give various statistics for a table and it’s columns, one of which is row count.

A direct count against the table will give an accurate row count but will hold locks potentially blocking other connections. For most scenarios, sp_spaceused or a DMV query is accurate enough. It is typically not accurate if other connections are modifying data and if that is the case, a direct table count might give you an accurate count that will soon be stale anyway.

Bitwise operations

There are still occasions when bit level manipulation is required in SQL Server.

One such place is the execution plan set options. If we return the plan attributes for a plan handle via: –


SELECT * FROM sys.dm_exec_plan_attributes(0x050006004108CF0D60ABDC3A0C00000001000000000000000000000000000000000000000000000000000000);

We get something like: –

Plan attributes

Each set option is assigned a unique value of 2 raised to the power of incrementing integers e.g. 2,4,8,16 etc. The full list is defined on the sys.dm_exec_plan_attributes MSDN page. Therefore any combination of set options can be defined in one 4-byte integer. This makes interpreting the results at a glance difficult and querying them more complex. The query is performed via the Bitwise AND operator. So for the example above where set_options = 4347, we would perform the following T-SQL select: –


WITH cteSetOptions (SetOption,Value) AS (
SELECT 'ANSI_PADDING',1 UNION ALL
SELECT 'Parallel Plan',2 UNION ALL
SELECT 'FORCEPLAN',4 UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL',8 UNION ALL
SELECT 'ANSI_WARNINGS',16 UNION ALL
SELECT 'ANSI_NULLS',32 UNION ALL
SELECT 'QUOTED_IDENTIFIER',64 UNION ALL
SELECT 'ANSI_NULL_DFLT_ON',128 UNION ALL
SELECT 'ANSI_NULL_DFLT_OFF',256 UNION ALL
SELECT 'NoBrowseTable',512 UNION ALL
SELECT 'TriggerOneRow',1024 UNION ALL
SELECT 'ResyncQuery',2048 UNION ALL
SELECT 'ARITH_ABORT',4096 UNION ALL
SELECT 'NUMERIC_ROUNDABORT',8192 UNION ALL
SELECT 'DATEFIRST',16384 UNION ALL
SELECT 'DATEFORMAT',32768 UNION ALL
SELECT 'LanguageID',65536 UNION ALL
SELECT 'UPON',131072 UNION ALL
SELECT 'ROWCOUNT',262144)

SELECT [Set Option] = SetOption + ' option is set'
FROM cteSetOptions
WHERE Value & 4347 = Value
ORDER BY [Set Option];

Giving the results: –

set option results

I have created a table valued function to pivot and return all plan attributes (both those defined in the set options single attribute and other explicit options).

First create the dbo.SetOptions table in the DBA database: –


USE DBA;

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM sys.tables WHERE [object_id] = OBJECT_ID(N'dbo.SetOptions'))
BEGIN;
DROP TABLE dbo.SetOptions;
END;

CREATE TABLE dbo.SetOptions (
SetOption VARCHAR(128) NOT NULL,
Value INT NOT NULL
);

INSERT INTO dbo.SetOptions
SELECT 'ANSI_PADDING',1 UNION ALL
SELECT 'Parallel Plan',2 UNION ALL
SELECT 'FORCEPLAN',4 UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL',8 UNION ALL
SELECT 'ANSI_WARNINGS',16 UNION ALL
SELECT 'ANSI_NULLS',32 UNION ALL
SELECT 'QUOTED_IDENTIFIER',64 UNION ALL
SELECT 'ANSI_NULL_DFLT_ON',128 UNION ALL
SELECT 'ANSI_NULL_DFLT_OFF',256 UNION ALL
SELECT 'NoBrowseTable',512 UNION ALL
SELECT 'TriggerOneRow',1024 UNION ALL
SELECT 'ResyncQuery',2048 UNION ALL
SELECT 'ARITH_ABORT',4096 UNION ALL
SELECT 'NUMERIC_ROUNDABORT',8192 UNION ALL
SELECT 'DATEFIRST',16384 UNION ALL
SELECT 'DATEFORMAT',32768 UNION ALL
SELECT 'LanguageID',65536 UNION ALL
SELECT 'UPON',131072 UNION ALL
SELECT 'ROWCOUNT',262144;

ALTER TABLE dbo.SetOptions ADD CONSTRAINT pkSetOptions PRIMARY KEY CLUSTERED (SetOption) WITH FILLFACTOR = 100;
ALTER TABLE dbo.SetOptions ADD CONSTRAINT ukSetOptions UNIQUE NONCLUSTERED (Value) WITH FILLFACTOR = 100;
GO

Then create the dbo.fn_Plan_Attributes function: –


USE DBA;
GO

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;

IF EXISTS (SELECT 1 FROM sys.sql_modules WHERE [object_id] = OBJECT_ID(N'dbo.fn_Plan_Attributes'))
BEGIN;
DROP FUNCTION dbo.fn_Plan_Attributes;
END;
GO

/*
Name: dbo.fn_Plan_Attributes

(C) Andy Jones

mailto:andrew@aejsoftware.co.uk

Description: -

Returns all plan attributes from sys.dm_exec_plan_attributes pivoted into a single row result-set.

Example usage: -

SELECT TOP 10 cp.plan_handle,cp.usecounts ,cp.size_in_bytes ,cp.cacheobjtype,cp.objtype, pa.*
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY dbo.fn_Plan_Attributes(cp.plan_handle) AS pa

Change History: -
1.0 06/11/2015 Created.

License: free to download and use providing this header is kept in place. Author consent is required for
any sale or re-distibution.
*/
CREATE FUNCTION dbo.fn_Plan_Attributes (@plan_handle VARBINARY(64)) RETURNS TABLE AS

RETURN

WITH cteSetOptions (PlanAttribute, Value) AS (
SELECT DISTINCT CASE pa.attribute WHEN N'set_options' THEN o.SetOption ELSE pa.attribute END,
CASE pa.attribute WHEN N'set_options' THEN CASE WHEN o.Value & CAST(pa.Value AS INT) = o.Value THEN 'Set' ELSE 'Not set' END ELSE pa.value END
FROM dbo.SetOptions AS o
CROSS JOIN sys.dm_exec_plan_attributes(@plan_handle) AS pa)

SELECT [acceptable_cursor_options],[ANSI_NULL_DFLT_OFF],[ANSI_NULL_DFLT_ON],[ANSI_NULLS],[ANSI_PADDING],[ANSI_WARNINGS],[ARITH_ABORT],[compat_level],[CONCAT_NULL_YIELDS_NULL],[date_first],[date_format],[DATEFIRST],[DATEFORMAT],[dbid],[dbid_execute],[FORCEPLAN],[free_cursors],[free_exec_context],[hits_cursors],[hits_exec_context],[inuse_cursors],[inuse_exec_context],[is_replication_specific],[language_id],[LanguageID],[merge_action_type],[misses_cursors],[misses_exec_context],[NoBrowseTable],[NUMERIC_ROUNDABORT],[objectid],[optional_clr_trigger_dbid],[optional_clr_trigger_objid],[optional_spid],[Parallel Plan],[parent_plan_handle],[QUOTED_IDENTIFIER],[removed_cursors],[removed_exec_context],[required_cursor_options],[ResyncQuery],[ROWCOUNT],[sql_handle],[status],[TriggerOneRow],[UPON],[user_id]
FROM
(SELECT PlanAttribute, Value
FROM cteSetOptions) AS cteSource
PIVOT
(
MAX(Value)
FOR PlanAttribute IN ([acceptable_cursor_options],[ANSI_NULL_DFLT_OFF],[ANSI_NULL_DFLT_ON],[ANSI_NULLS],[ANSI_PADDING],[ANSI_WARNINGS],[ARITH_ABORT],[compat_level],[CONCAT_NULL_YIELDS_NULL],[date_first],[date_format],[DATEFIRST],[DATEFORMAT],[dbid],[dbid_execute],[FORCEPLAN],[free_cursors],[free_exec_context],[hits_cursors],[hits_exec_context],[inuse_cursors],[inuse_exec_context],[is_replication_specific],[language_id],[LanguageID],[merge_action_type],[misses_cursors],[misses_exec_context],[NoBrowseTable],[NUMERIC_ROUNDABORT],[objectid],[optional_clr_trigger_dbid],[optional_clr_trigger_objid],[optional_spid],[Parallel Plan],[parent_plan_handle],[QUOTED_IDENTIFIER],[removed_cursors],[removed_exec_context],[required_cursor_options],[ResyncQuery],[ROWCOUNT],[sql_handle],[status],[TriggerOneRow],[UPON],[user_id])
) AS t;

GO

An example select statement from the plan cache using this function is: –


USE DBA;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT ObjectName = OBJECT_NAME(st.objectid, st.[dbid]) ,
cp.plan_handle,
cp.usecounts ,
cp.size_in_bytes ,
cp.cacheobjtype ,
cp.objtype ,
qp.query_plan ,
st.[text],
pa.*
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY dbo.fn_Plan_Attributes(cp.plan_handle) AS pa
WHERE st.[dbid] = DB_ID(N'AdventureWorks2012')
AND st.objectid = OBJECT_ID(N'AdventureWorks2012.dbo.uspGetBillOfMaterials');