Retrieving Default Values from Default Constraint Expressions from SQL Server Tables

Sometimes you need to get the current default values (not the expressions) from a table in SQL Server. Unfortunately SQL Server seems to not have a function to get back the result value of a default constraint expression and then saving the expression in case of constant values is also a little bit strange.

That is, a numeric value "1" would result in a default expression "((1))". This is what you also get back if you query a table using system tables/views/functions to find out the default of a column. You could of course begin to parse the result like in the example, removing the brackets and find the value in the middle.

But what if your default constraint is something like "((getdate()))"? You would get back the text "getdate()" which is not useful for your frontend because this one expects the value, not the expression. The same if your default constraint expression contains the call of a UDF.

I searched a lot to find a standard solution provided by SQL Server itself but couldn't find one, so I decided to create an own procedure for this purpose. Here's the result:
 

SET ANSI_NULLS ON
                      GO
                      SET QUOTED_IDENTIFIER ON
                      GO
                      -- =============================================
                      -- Author:		Christian Coppes
                      -- Create date: 30.12.2014
                      -- Description:	Returns a table with the fields of
                      --				the desired source table and their default values 
                      --				as sql_variant datatype
                      -- =============================================
                      CREATE PROCEDURE dbo.procDefaultValues_Get 
                      	@strSchemaName nvarchar(128) = '', 
                      	@strTableName nvarchar(128) = ''
                      WITH EXECUTE AS 'UserWithPermission'
                      AS
                      BEGIN
                      	-- SET NOCOUNT ON added to prevent extra result sets from
                      	-- interfering with SELECT statements.
                      	SET NOCOUNT ON;
                      
                      	DECLARE @strColumnName nvarchar(128);
                      	DECLARE @strDefault nvarchar(MAX);
                      	DECLARE @strSQL nvarchar(MAX) ='';
                      	DECLARE @intCount AS int = 1;
                      	DECLARE @intMaxCount AS int = 0;
                      	DECLARE @tblDefaults AS table (ID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
                      								   ColumnName nvarchar(128),
                      								   DefaultText nvarchar(MAX));
                      
                      	-- write the column name and default expressions for all columns with default constraint
                      	-- into the temp table variable @tblDefaults
                      	-- (changed to INFORMATION_SCHEMA method, thanks for the suggestion to Mark Wills)
                      	INSERT INTO @tblDefaults (ColumnName,DefaultText)
                      		SELECT column_name, column_default
                      		  FROM INFORMATION_SCHEMA.COLUMNS
                      		 WHERE table_schema = @strSchemaName
                      		   AND table_name = @strTableName
                      		   AND column_default IS NOT NULL
                      
                      	-- Use this for the INSERT command above if the INFORMATION_SCHEMA method above does not work
                      	--SELECT	SCOL.name,SCOM.text
                      	--FROM	syscomments AS SCOM
                      	--INNER JOIN (SELECT	SC.cdefault,SC.name
                      	--			FROM	syscolumns AS SC
                      	--			WHERE	SC.id = OBJECT_ID(@strSchemaName + '.' + @strTableName)
                      	--					AND SC.cdefault > 0
                      	--		   ) AS SCOL
                      	--ON		SCOM.id = SCOL.cdefault
                      
                      	-- create a temp table which can be used (and extended) 
                      	-- inside the dynamic SQL string and insert one row
                      	CREATE TABLE #tmp (ID int PRIMARY KEY NOT NULL)
                      	INSERT INTO #tmp (ID) VALUES (1);
                      
                      	-- determine the loop counter end
                      	SELECT @intMaxCount = COUNT(*) FROM @tblDefaults;
                      
                      	-- only start loop if there are any defaults in the table
                      	IF @intMaxCount > 0
                      	BEGIN
                      		SET @intCount = 1;
                      		SET @strSQL = '';
                      
                      		-- loop through all rows of the default table
                      		-- and create one additional field in the #tmp table
                      		-- with datatype "sql_variant" so that the output to the frontend
                      		-- contains the right datatype and no conversion between
                      		WHILE @intCount <= @intMaxCount
                      		BEGIN
                      			SET @strSQL = @strSQL + (SELECT D.ColumnName FROM @tblDefaults AS D WHERE D.ID = @intCount) + ' sql_variant,'
                      			SET @intCount = @intCount + 1;
                      		END
                      		SET @strSQL = LEFT(@strSQL,LEN(@strSQL)-1)
                      		SET @strSQL = 'ALTER TABLE #tmp ADD ' + @strSQL
                      		EXECUTE sp_executesql @strSQL
                      
                      		SET @strSQL = '';
                      		SET @intCount = 1;
                      		-- loop again through all rows of the default table
                      		-- and update the value of the corresponding field 
                      		-- with the evalued return value which results from the default expression
                      		WHILE @intCount <= @intMaxCount
                      		BEGIN
                      			SET @strColumnName = (SELECT D.ColumnName FROM @tblDefaults AS D WHERE D.ID = @intCount);
                      			SET @strDefault = (SELECT DefaultText FROM @tblDefaults AS D WHERE D.ID = @intCount);
                      			SET @strSQL = 'DECLARE @strResult AS sql_variant; SELECT @strResult = ' + @strDefault + ';';
                      			SET @strSQL = @strSQL + 'UPDATE #tmp SET ' + @strColumnName + ' = @strResult WHERE ID = 1;'
                      			PRINT @strSQL
                      			EXECUTE sp_executesql @strSQL
                      			SET @intCount = @intCount + 1;
                      		END
                      	END
                      
                      	-- as the #tmp table has always another number of columns it is important to keep "SELECT *" here
                      	SELECT * FROM #tmp;
                      	DROP TABLE #tmp;
                      END
                      GO

Select all Open in new window

This should work universally in all SQL databases with all tables. When you execute the SP you only need to provide the schema name and the table name as parameters. You always get back a table with exactly one row with at least a column "ID" and the value "1" for the ID. For each column in the table you specified that has a default constraint you will get back a column in this table with the datatype "sql_variant". The value will not be the default expression but instead the calculated value, so for "getdate()" it would be the value of the current date and time.

The important thing here is that "sql_variant" allows you to get the calculated value back in the way the original datatype is defined. That is especially important in case of date/time values because if the column datatypes would have been defined as i.e. nvarchar(MAX) then SQL Server would automatically format the date in the way the regional settings were defined; there are tons of date formats out there so if it is not the expected format you would need to parse the returned text to restore the original date/time value. With sql_variant you don't need that because you can now retrieve the value in original format that can be directly assigned to a field (i.e. in Access in an unbound form).

In Access, for example, ADO can be used to execute the SP on the server (there are lots of tutorials how to do that so I do not repeat that step here) and assign that to an ADO recordset. As the SP always returns at least one row in any case you do not need to check for an empty recordset (rs.EOF =True and rs.BOF = True), and because all fields in the table have the same name and with sql_variant also a usable datatype you can simply access the desired field's default value with something like:
 

rs.Fields("MyTableField").Value

Select all Open in new window

You can then assign to the "DefaultValue" property of a control or directly assign it to its value property.

Please note that normally Access can do that on its own. In a normal linked table form or ADP form there is a property "Fetch Defaults"; if that is set to "Yes" Access automatically makes a roundtrip to the server and fetches the default values (not expressions) for each field. Unfortunately that needs the permissions to do that on the underlying table of a view or SP that is not always present. Moreover an unbound form doesn't know anything about the needed object so you would not get any default values even with "Fetch Defaults" switched to "Yes". Also if you use other kinds of frontends they do not necessarily provide a way to get these values on their own.

The SP also contains a "WITH EXECUTE AS" clause at the top which you only need if the user which executes the SP has lower permissions on the tables. With this you can raise the permissions to a user whose name you can specify here. If you don't need that, simply remove that line from the code.

EDIT: Thanks to Mark Wills for reviewing this and the valuable hint to not use "syscomments" anymore for newer versions of SQL Server. I changed the code above to use the recommended INFORMATION_SCHEMA view instead to determine the default constraint expressions for now. The former version is still available in remarks in the code above if you use an older SQL Server version which maybe does not work with the INFORMATION_SCHEMA method.

By the way I want to add that the code was created and tested with SQL Server 2008 R2, if the current or former version of the code does not work for you or if you have another code variant which improves the current one, feel free to add a comment to this article. As always, there are X possible ways of doing the same and this one may not be the best way, so any comment is welcome.