tag:blogger.com,1999:blog-902486075140485537.post4419824845981775947..comments2023-09-15T16:47:49.946+01:00Comments on Piotr Rodak:<br> if datepart(dw, getdate()) in (6, 7) use pubs;: Coding practices - Call your constraintsPiotr Rodakhttp://www.blogger.com/profile/08418009093976236989noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-902486075140485537.post-68167721680257028132010-07-10T13:31:06.284+01:002010-07-10T13:31:06.284+01:00Thanks Saggi, this is clever idea.Thanks Saggi, this is clever idea.Piotr Rodakhttps://www.blogger.com/profile/08418009093976236989noreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-9547505729833458602010-07-10T11:37:16.626+01:002010-07-10T11:37:16.626+01:00Hey Piotr,
I agree that fetching constraint names...Hey Piotr,<br /><br />I agree that fetching constraint names in order to alter or drop them (usually when you want to alter a column that is constrained by them) is an annoyance. I wrote this little script for a customer who wanted to rename existing default constraints (he didn't have any check constraints) to reasonable names:<br /><br /><br />DECLARE @RenameCommand NVARCHAR(MAX)<br /><br />DECLARE z CURSOR LOCAL FAST_FORWARD FOR <br />SELECT RenameCommand = 'exec sp_rename ' + QUOTENAME(DefaultName,'''') + ' , ' + QUOTENAME('DF_' + TableName + '_' + ColumnName,'''')<br />FROM (<br />SELECT <br /> DefaultName = dc.name <br /> , ColumnName = c.name <br /> , DefaultDefinition = dc.definition <br /> , TableName = OBJECT_NAME(c.object_id)<br /> , SchemaName = OBJECT_SCHEMA_NAME(c.object_id)<br />FROM sys.default_constraints dc<br /> INNER JOIN sys.columns c ON<br /> dc.parent_column_id = c.column_id<br /> AND dc.parent_object_id = c.object_id <br /> INNER JOIN sys.tables tbl ON<br /> tbl.object_id = c.object_id<br />WHERE CAST(<br /> case <br /> when tbl.is_ms_shipped = 1 then 1<br /> when (<br /> select <br /> major_id <br /> from <br /> sys.extended_properties <br /> where <br /> major_id = tbl.object_id and <br /> minor_id = 0 and <br /> class = 1 and <br /> name = N'microsoft_database_tools_support') <br /> is not null then 1<br /> when OBJECTPROPERTYEX(tbl.object_id,'IsSystemTable')=1 then 1<br /> else 0<br /> end AS bit)=0<br /> ) c<br /><br />OPEN z;<br /><br />WHILE 1=1<br />BEGIN<br /> FETCH NEXT FROM z INTO @RenameCommand<br /> IF @@FETCH_STATUS!=0<br /> BREAK;<br /> <br /> PRINT @RenameCommand<br /> EXEC(@RenameCommand)<br />END<br /><br />CLOSE z;<br />DEALLOCATE z;<br /><br /><br />----<br /><br />Cheers,<br />S. NeumannSaggi Neumannhttp://www.valinor.co.ilnoreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-57274141587138790432010-07-01T21:50:48.654+01:002010-07-01T21:50:48.654+01:00Thanks Dennis, I knew that this framework exists, ...Thanks Dennis, I knew that this framework exists, but never had opportunity to have a closer look at it. I will give it a try :)Piotr Rodakhttps://www.blogger.com/profile/08418009093976236989noreply@blogger.comtag:blogger.com,1999:blog-902486075140485537.post-29026226291495592882010-07-01T20:34:56.421+01:002010-07-01T20:34:56.421+01:00Good post Piotr - I completely agree, not naming c...Good post Piotr - I completely agree, not naming constraints causes a lot of little annoyances. It even makes it rather difficult to compare two databases (say a model and a production database) to see if they have the same schema. I noticed in your other post, "Good practices - database programming, unit testing" you were also testing your constraints using your own hand-rolled testing techniques. You may be interested to know about the tSQLt unit testing framework. It lets you do all your unit testing in TSQL (http://www.tsqlt.org), but provides the nice framework pieces that are missing from hand-rolling your own.Dhttps://www.blogger.com/profile/14705791663280251900noreply@blogger.com