When I was troubleshooting a database developed by a supplier according to regulations last week, we needed to view each of the 50 tables in their database, to ensure that all fields that are expected to be the default value are assigned the default value. You can imagine how daunting this is, and I immediately raised this question. Is there
When I was troubleshooting a database developed by a supplier according to regulations last week, we needed to view each of the 50 tables in their database, to ensure that all fields that are expected to be the default value are assigned the default value. You can imagine how daunting this is, and I immediately raised this question. Is there
Expert answers
By querying three system tables in any database, you can obtain the default values of each field in each table. The following is the core query. It returns the default value allocated to each user table in the current database. This query is compatible with SQL 2000 and SQL 2005.
Quote:
The Code is 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 table metadata for us. In this example, we are only interested in table names. The syscolumns table stores metadata associated with each field of each table. In this example, we only need the field name. Finally, the default metadata is provided by the syscomments table.
Run this query on the Northwind database to generate the following results (for the sake of simplicity, some records are omitted ). Note: Because LEFT joins the syscomments table, it returns the default value of NULL.
Now I am thinking about the options for this good basic query version...
Select 1: Search for special default values
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 information with default fields is returned.
Modify the WHERE Condition Statement of the core query to ignore the NULL value in the syscomments. text table. This technique is as follows:
Quote:
The Code is 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 join from a left join to an inner join in the FROM Condition Clause provides optimization:
Quote:
The Code is 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
Another option is to use the system directory view in SQL 2005. The previous query provided me with the required information, which can be used in SQL 2000 and SQL 2005, you can mine additional metadata associated with this default value (actually a default constraint) in the SQL2000 instance. By specifying this query in the system directory view, we can obtain additional information not displayed in the previous query.
Quote:
The Code is as follows:
Select st. [name] AS "Table Name", SC. [name] AS "Column Name", SD. definition AS "Default Value", SD. [name] AS "Constraint Name"
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, because you are told that there is no better way to study it by your instinct as a database administrator. You will never know what you may get.
