Default Constraints with PRINT or DROP

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

  • Is there anything like this for sql server 2000?

    ¤ §unshine ¤

  • 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]

  • 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 3 (of 3 total)

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