Default Constraints with PRINT or DROP

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Satyanarayana... » Default Constraints with PRINT or DROP

Default Constraints with PRINT or DROP

Rate Topic

Display Mode

Topic Options

Author

Message

Satyanarayana B

Posted Monday, September 24, 2007 11:41 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, February 02, 2013 2:41 AM
Points: 48, Visits: 25

Comments posted to this topic are about the item Default Constraints with PRINT or DROP

Post #402174

sunshine-587009

Posted Monday, April 14, 2008 1:50 PM

Old Hand

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 PM
Points: 360, Visits: 1,072

Is there anything like this for sql server 2000?

¤ §unshine ¤

Post #484629

greg-674106

Posted Thursday, March 18, 2010 1:28 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 18, 2010 1:09 PM
Points: 4, Visits: 2

A partial correction to the current script that is returning additional and unnecessary rows. This is in reference to another article from the same author.

Script adjusted to:
SELECT IDENTITY(INT, 1, 1) as Row_ID, CONVERT(VARCHAR(50), schema_name(t.schema_id)) as Schema_Name, CONVERT(VARCHAR(250), t.Name) as Table_Name, CONVERT(VARCHAR(120), c.Name) as Column_Name, CONVERT(VARCHAR(120), d.Name) as Default_Constraints_Name, CONVERT(VARCHAR(250), d.definition) as Default_Value
INTO #Defaults FROM sys.default_constraints d
LEFT JOIN sys.tables t ON d.parent_object_id = t.object_id
LEFT JOIN sys.columns c ON t.object_id = c.object_id AND d.parent_column_id = c.column_id
WHERE (@TablesName = '' OR CONVERT(VARCHAR(250), t.Name) = @TablesName) AND (@ColumnName = '' OR CONVERT(VARCHAR(250), c.Name) = @ColumnName)

The current script also returned the following line. I did not bother adjusting the script since it was easier for me to simply clear that line. If you need this in an automated script, you need to handle such returns...
ALTER TABLE [dbo].[dtproperties] ADD CONSTRAINT [DF__dtpropert__versi__77FFC2B3] DEFAULT (0) FOR [version]

Post #885858

sunshine-587009

Posted Monday, March 22, 2010 9:38 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 PM
Points: 360, Visits: 1,072

Thank you. I have not had a chance to work on this last week, My code is on the computer at work and I will be picking it up tomorrow. I'll keep you posted!

¤ §unshine ¤

Post #887462

« Prev Topic | Next Topic »

Permissions