Default Value of SQL Server query field

When the database is troubleshooting according to regulations, you need to view each of the 50 tables in the database to ensure that all fields that are expected to be the default value are assigned the default value. It can be imagined that this is a daunting task. Is there a better way to view this schema than opening every table in the SQL server administration suite?
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.
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 the example of the exam, only the table name is of interest. 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.
Select 1: Search for special default values
By editing the where Condition Statement, we can view the special default values in all tables.
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:
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:
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.
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
I was 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