Expert answers
By querying the three system tables in any database, you can get the default values for each field in each table. Here is the core query. It returns the default value assigned to each user table in the current database. This query is compatible with SQL 2000 and SQL 2005.
Quote:
Copy Code code as follows:
SELECT so.name as "Table name", Sc.name as "Column name", SM. TEXT as "Default Value"
From dbo.sysobjects so INNER JOIN dbo.syscolumns SC on so.id = sc.id
Left JOIN dbo.syscomments SM on sc.cdefault = sm.id
WHERE so.xtype = ' U '
Order by So. [Name], sc.colid
sysobjects provides us with table metadata. In this example, we are only interested in the table name. The syscolumns table stores the metadata associated with each field in each table. In this example, we just need the name of the field. Finally, the default value metadata is provided by the syscomments table.
Running this query on the Northwind database generates the following results (for brevity, some records are omitted). Note that because the left join is to the syscomments table, it returns a null default value.
Now I'm thinking about the choice of this very good basic query version ...
Select 1: Search for a special default value
By editing the Where Condition statement, we can view the special default values in all tables.
Quote:
SELECT so.name as "Table name", Sc.name as "Column name", SM. TEXT as "Default Value"
From dbo.sysobjects so INNER JOIN dbo.syscolumns SC on so.id = sc.id
Left JOIN dbo.syscomments SM on sc.cdefault = sm.id
WHERE so.xtype = ' U ' and SM. TEXT = ' (0) '
Order by So. [Name], sc.colid
Select 2: Only return information with default value fields
Modify the Where condition statement of the core query to ignore the null value in the Syscomments.text table, as shown in the following technique:
Quote:
Copy Code code as follows:
SELECT so.name as "Table name", Sc.name as "Column name", SM. TEXT as "Default Value"
From dbo.sysobjects so INNER JOIN dbo.syscolumns SC on so.id = sc.id
Left JOIN dbo.syscomments SM on sc.cdefault = sm.id
WHERE so.xtype = ' U ' and SM. TEXT is not NULL
Order by So. [Name], sc.colid
However, changing a join from a from conditional clause from a LEFT join to a INNER join provides optimizations:
Quote:
Copy Code code as follows:
SELECT so.name as "Table name", Sc.name as "Column name", SM. TEXT as "Default Value"
From dbo.sysobjects so INNER JOIN dbo.syscolumns SC on so.id = sc.id
INNER JOIN dbo.syscomments SM on sc.cdefault = sm.id
WHERE so.xtype = ' U '
Order by So. [Name], sc.colid
There is actually another option to take advantage of the system catalog view in SQL 2005. The previous query provides me with the information I need at this point and can be used in SQL 2000 and SQL 2005, where the additional metadata associated with this default value (which is actually a default constraint) can be mined in the SQL2000 instance. By using this query specifically on the system catalog view, we can get additional information that was not shown in the previous query.
Quote:
Copy Code code as follows:
SELECT St.[name] as "Table name", Sc.[name] as "Column name", sd.definition as "Default Value", Sd.[name] as "Constraint N Ame
From Sys.tables ST INNER JOIN sys.syscolumns SC on st.[object_id] = Sc.[id]
INNER JOIN sys.default_constraints SD on st.[object_id] = sd.[parent_object_id] and sc.colid = sd.parent_column_id
Order by St.[name], Sc.colid
So remember, just because you are told that there is no better way to rely on your instincts as a database administrator to delve into. You never know what you might get.
