Default Constraints with PRINT or DROP

  • Satyanarayana B

    SSC Enthusiast

    Points: 152

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

  • sunshine-587009

    SSCrazy Eights

    Points: 8815

    Is there anything like this for sql server 2000?

    ¤ §unshine ¤

  • greg-674106

    Valued Member

    Points: 50

    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]

  • sunshine-587009

    SSCrazy Eights

    Points: 8815

    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 ¤

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply