September 24, 2007 at 11:41 am
Comments posted to this topic are about the item Default Constraints with PRINT or DROP
April 14, 2008 at 1:50 pm
Is there anything like this for sql server 2000?
¤ §unshine ¤
March 18, 2010 at 1:28 pm
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]
March 22, 2010 at 9:38 am
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