Statements that list all fields with default values in SQL Server

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:
Copy codeThe 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:
Copy codeThe 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:
Copy codeThe 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:
Copy codeThe 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.