Shulou(Shulou.com)05/31 Report--

This article shows you what are the statements of all the fields with default values in SQL Server, which are concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

By querying 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 values assigned to each user table in the current database. This query is compatible in both SQL 2000 and SQL 2005. Quote: copy the 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 table names. The syscolumns table stores metadata associated with individual fields of each table. In this example, we only need the field name. Finally, the default value metadata is provided by the syscomments table. Run this query against the Northwind database to produce the following results (some records are omitted for brevity). Note that because LEFT JOIN goes to the syscomments table, it will return the NULL default value. Now I wonder if there are any options for this good basic query version. Option 1: search for special default values by editing the WHERE conditional 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 option 2: only return information with default fields modify the WHERE conditional statement of the core query to ignore the null value in the syscomments.text table The trick is as follows: Quote: copy the 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

But, Changing the JOIN in the FROM conditional clause from a LEFT JOIN to an INNER JOIN provides optimization: Quote: the copied 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

In fact, there is another option, using the system catalog view in SQL 2005. The previous query provides me with the information I need at this time, which can be used in both SQL2000 and SQL 2005, and the extra metadata associated with this default (which is actually a default constraint) can be mined in the SQL2000 instance. By making this query specific to the system catalog view, we can get additional information that was not shown in the previous query. Quote: the copy 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, just because you are told there is no better way to rely on your instinct as a database administrator to delve into it. You never know what you might get.

What are the statements of all the fields with default values in SQL Server? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.