Script for Default Constraints

Question

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

SSCertifiable

Points: 7822

June 10, 2008 at 9:21 am

Select * from sys.default_constraints

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 2

I'm sorry, this is for sql server 2000 and I need a script that will create the alter statements to add them to the database in case of loss.

¤ §unshine ¤

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

See if this helps.

Create Table #Temp

(TableName varchar(100),

ColumnName varchar(100),

DefaultName varchar(500),

DefaultText varchar(100)

)

INSERT INTO #Temp

Exec sp_msforeachtable'

Select ''?'' TableName ,c.Name, object_name(c.cdefault) DefaultName,

t.text

from dbo.syscolumns c

join dbo.syscomments t on t.id = c.cdefault and t.colid = 1

and not exists (select * from dbo.syscomments where id = c.cdefault and colid = 2)

'

Select * from #Temp

Drop Table #Temp

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 4

thats great. Is there a way to add the alter table add constraint in there?

¤ §unshine ¤

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 5

Create Table #Temp

(Script varchar(1000)

)

INSERT INTO #Temp

Exec sp_msforeachtable'

Select ''Alter Table '' + ''?'' + '' ADD CONSTRAINT '' + object_name(c.cdefault) + '' DEFAULT '' + t.text + '' FOR '' + c.Name AS Script

from dbo.syscolumns c

join dbo.syscomments t on t.id = c.cdefault and t.colid = 1

and not exists (select * from dbo.syscomments where id = c.cdefault and colid = 2)

'

Select * from #Temp

Drop Table #Temp

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 6

You are awesome! Thank you so much! You should post as a script in the scripts section.

Thanks again!

¤ §unshine ¤