|
|
SQL Server T-SQL User-Defined Function of the Week
Getting a Table of Defaults in your Database
Volume 1 Number 15 February 25, 2003
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
Do you use defaults? I do. In almost every table.
Defining a default for a column insures that a value will be
assigned to the column even if an INSERT statement doesn't
provide one. When a column doesn't allow NULLs, that prevents
the INSERT from being rejected.
Every once in a while, I review the defaults in my database
using this week's UDF, udf_SQL_DefaultsTAB. Occasionally a
issue will show up when I run it. For example, a column that
allows NULL, but has a default. While that's allowable, it
usually indicates confusion on the part of database designer.
I originally coded the UDF to get the necessary information
from the system tables with this query:
SELECT TOP 100 PERCENT WITH TIES
Tab.[name] as [Table] -- Table that has this default
, Col.[name] as [Column] -- Column the default is on
, Def.[name] as [Default]-- Name of the default
, Cmt.[text] as [Value] -- Expression to create the value.
FROM sysobjects Def -- The default objects
INNER JOIN sysconstraints Cns -- = A constraint on a col
ON Def.id = Cns.constid
INNER JOIN syscolumns Col -- The column
ON Cns.colid = Col.colid
and Cns.id = Col.id
INNER JOIN sysobjects Tab -- table column is in
ON Col.id = Tab.id
INNER JOIN syscomments Cmt -- Txt of the expression
ON Def.id = Cmt.id
WHERE Def.type = 'D'
and Def.category = 2048
and Tab.[name] != 'dtproperties'
ORDER BY Tab.[name] -- The table
,Col.[name] -- The Column
As you can see, it takes a five way join to find all the
necessary information in the system tables. Two of those tables
are sysobjects, once for defaults (alias Def) and the other time
for tables (alias Tab).
After I had written it, something prompted me to look at the
documentation for INFORMATION_SCHEMA.COLUMNS and I found that
my work had been in vein. That view and already combined the
information that I want for the function and a much simpler
SELECT was all that I needed. Here's the CREATE FUNCTION script
for the revised UDF:
/------------- Copy From Below this line ----------------------\
CREATE FUNCTION dbo.udf_SQL_DefaultsTAB (
@Table_Name sysname = NULL -- The table to get defaults
-- for or NULL for all tables.
) RETURNS TABLE
/*
* Returns a table of all defaults that exist on all user
* tables in the database.
*
* Common Usage:
select * from udf_SQL_DefaultsTAB('CurrencyCD')
select * from udf_SQL_DefaultsTAB(default)
*
* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
*
* Published initially as T-SQL UDF Of the Week Vol 1 #15
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN
SELECT TOP 100 PERCENT WITH TIES
TABLE_NAME as [Table] -- Table that has this default
, COLUMN_NAME as [Column] -- Column the default is on
, DATA_TYPE as [Type] -- Data type of the column
, CASE WHEN IS_NULLABLE IS NULL THEN 'NO'
ELSE IS_NULLABLE END as [Nullable] -- Can be NULL?
, COLUMN_DEFAULT as [Value] -- Expression to get the value.
FROM INFORMATION_SCHEMA.COLUMNS Col
WHERE COLUMN_DEFAULT IS NOT NULL
and TABLE_NAME != 'dtproperties'
AND (@Table_Name is NULL
OR TABLE_NAME = @Table_Name)
ORDER BY TABLE_NAME
, COLUMN_NAME
GO
GRANT SELECT ON [dbo].[udf_SQL_DefaultsTAB] to PUBLIC
GO
\------------ Stop copying above this line --------------------/
In the WHERE clause, you'll notice that 'dtproperties' is
excluded from consideration. dtproperties is where SQL Server
stores database diagrams. Although it's a user table it should
only be used by the system. I don't know if it has any defaults
but I routinely exclude it from searches on user tables.
The next query runs the UDF to show the defaults in the database:
-- List all defaults
SELECT [Table], [Column], [Type], Nullable as [Null], [Value]
FROM udf_SQL_DefaultsTAB (default)
GO
\------------ Stop copying above this line --------------------/
(Results -- selected sample...)
Table Column Type Null Value
------------------ --------------- -------- ---- -------------
CalendarDefinition CreatedByUSERID varchar No (user_name())
CalendarDefinition CreatedDT datetime No (getdate())
CurrencyCD CreatedByUSERID varchar No (user_name())
CurrencyCD CreatedDT datetime No (getdate())
CurrencyRateTypeCD CreatedByUSERID varchar No (user_name())
CurrencyRateTypeCD CreatedDT datetime No (getdate())
CurrencySourceCD CreatedByUSERID varchar No (user_name())
CurrencySourceCD CreatedDT datetime No (getdate())
CurrencyXchange ExchangeRATE decimal No (0)
CurrencyXchange RecordedDT datetime No (getdate())
UnitsDefinition DerivedBIT bit No (0)
(End of results)
As you can see, several tables have columns named
CreatedByUSERID and CreatedDT. In fact, I put five columns into
every table that's used to hold what I consider application
configuration information. Tables such as code tables,
employee tables, company tables, or any table where the rows
stay around for a long time and are occasionally updated. The
five columns are:
Name Data type Description
---------------- ------------- ---------------------------------
CreatedByUSERID nvarchar(128) User that INSERTed the row
CreatedDT smalldatetime When the row was INSERTED
LastUpByUSERID nvarchar(128) User that last UPDATEed the row
LastUpDT smalldatetime When the row was last UPDATed
RowVrsn timestamp System timestamp
The CreatedByUSERID and CreatedDT columns can always be set
with a default. The LastUpByUSERID and LastUpDT columns must
be set in a trigger. Finally, RowVrsn doesn't need a default.
As a timestamp data type it's set by SQL Server when the row
is INSERTED and on every UPDATE.
Due to the size, I usually don't put these columns into tables
that hold high volume transactions, unless the application really
requires it. Of course, a full blown audit facility would make
the USERID and DT fields unnecessary. Without auditing
they're valuable in tracking down many problems that an
application might experience.
I've learned my lesson. I'll always look in INFORMATION_SCHEMA
before spending any time searching through system tables.
That's all for this issue. Don't forget to share this issue with
anyone who is interested in SQL Server.
+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
+--------------------------------------------------------------+
|
|
