Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Default Constraints with PRINT or DROP Expand / Collapse
Author
Message
Posted Monday, September 24, 2007 11:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:07 AM
Points: 48, Visits: 26
Comments posted to this topic are about the item Default Constraints with PRINT or DROP
Post #402174
Posted Monday, April 14, 2008 1:50 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:37 AM
Points: 361, Visits: 1,162
Is there anything like this for sql server 2000?

¤ §unshine ¤
Post #484629
Posted Thursday, March 18, 2010 1:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, March 22, 2010 9:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:37 AM
Points: 361, Visits: 1,162
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 »

Add to briefcase

Permissions Expand / Collapse