Search results matching tags 'Developer' and 'TSQL'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Developer,TSQL&orTags=0Search results matching tags 'Developer' and 'TSQL'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Cleaning up Un-Named Defaultshttp://sqlblog.com/blogs/andrew_kelly/archive/2009/06/26/cleaning-up-un-named-defaults.aspxFri, 26 Jun 2009 16:07:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:14944Andrew Kelly<P>I just had a situation in which we are automating the comparing of databases and I came across a bunch of Defaults that were never explicitly named. This causes them to get a new name each time you create them and makes it much more difficult to write future update scripts when you don’t know the actual name it will be. In any case I decided to clean them up and since there were about a hundred I didn’t want to create all the cleanup code by hand and wrote a little script to generate the DROP &amp; ADD’s which I figured others may find useful as well. The code would look much better if Windows Live Writer would keep the rich text when pasting into it but it doesn’t by default. There are some plug in’s to allow this but I have had little success so far in getting ones to install that actually do what I want. I will just have to deal with that later when I have more time. </P>
<P>The code below simply adds the necessary meta-data to the temp table that I need to Drop and Add the default constraints. I then run a select statement that dynamically builds each Drop or Add using that data and the resulting text can be used as a script to do the cleanup.&nbsp; It’s pretty simple stuff but may save some typing for anyone else wishing to cleanup messes like this that lazy developers :) and SSMS create. In my opinion ALL objects should be explicitly named at the time of initial creation and nothing should be left to be auto-generated by the tool or the engine. It will only lead to heartache down the road.&nbsp; And as always, test any code you find here first…</P>
<P>&nbsp;</P>
<P><SPAN style="FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;">SET NOCOUNT ON ; <o:p></o:p></SPAN></P>
<P><SPAN style="FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;">CREATE TABLE #DF ([Table Name] NVARCHAR(128), [Column Name] NVARCHAR(128), <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Default Name] NVARCHAR(128), [definition] NVARCHAR(200)) ; <o:p></o:p></SPAN></P>
<P><SPAN style="FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;">INSERT INTO #DF ([Table Name], [Column Name], [Default Name], [definition] ) <BR>SELECT&nbsp; object_name(d.[parent_object_id]) AS [Table Name], c.[name] AS [Column Name], <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; d.[name] AS [Default Name], d.[definition] <BR>&nbsp; FROM sys.columns AS c INNER JOIN sys.default_constraints AS d <BR>&nbsp;&nbsp;&nbsp; ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id] <BR>AND d.is_system_named = 1 <o:p></o:p></SPAN></P>
<P><SPAN style="FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;">--&nbsp;&nbsp; Drop the existing constraints <BR>SELECT 'DECLARE @DF_Name NVARCHAR(128) ;' <o:p></o:p></SPAN></P>
<P><SPAN style="FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;">SELECT N'SELECT @DF_Name = d.[name]&nbsp; from sys.columns AS c INNER JOIN sys.default_constraints AS d <BR>&nbsp;&nbsp;&nbsp; ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id] <BR>WHERE c.[object_id] = object_id(''' + [Table Name] + N''') AND c.[name] = N''' + [Column Name] + N''' ; <o:p></o:p></SPAN></P>
<P><SPAN style="FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;">IF @DF_Name IS NOT NULL <BR>BEGIN <BR>&nbsp;&nbsp; EXEC(''ALTER TABLE [dbo].[' + [Table Name] + N'] DROP CONSTRAINT ['' + @DF_Name + '']'') ; <BR>END ; <o:p></o:p></SPAN></P>
<P><SPAN style="FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;">' <BR>FROM #DF <BR>ORDER BY [Table Name]; <o:p></o:p></SPAN></P>
<P><SPAN style="FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;">--&nbsp;&nbsp; Create the New constraints <BR>SELECT N'IF NOT EXISTS(SELECT * FROM sys.columns AS c INNER JOIN sys.default_constraints AS d <BR>&nbsp;&nbsp;&nbsp; ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id] <BR>WHERE c.[name] = ''' + [Column Name] + N''' AND OBJECT_NAME(d.[parent_object_id]) = ''' + [Table Name] + N''') <BR>BEGIN <BR>&nbsp;&nbsp;&nbsp; ALTER TABLE [' + [Table Name] + N'] ADD&nbsp; CONSTRAINT [DF_' + [Table Name] + N'_' <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; + [Column Name] + N']&nbsp; DEFAULT ' + [definition] +&nbsp; N'FOR [' + [Column Name] + N'] ; <BR>END ; <o:p></o:p></SPAN></P>
<P><SPAN style="LINE-HEIGHT:115%;FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA;">' <BR>FROM #DF <BR>ORDER <SPAN style="LINE-HEIGHT:115%;FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA;">BY [</SPAN><SPAN style="LINE-HEIGHT:115%;FONT-FAMILY:'Comic Sans MS';FONT-SIZE:7.5pt;mso-bidi-font-family:Arial;mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA;">Table Name];</SPAN></SPAN></P>
<P><SPAN style="LINE-HEIGHT:115%;FONT-FAMILY:'Calibri','sans-serif';FONT-SIZE:10pt;mso-ascii-theme-font:minor-latin;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Arial;mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA;"><SPAN style="LINE-HEIGHT:115%;FONT-FAMILY:'Comic Sans MS';FONT-SIZE:7.5pt;mso-bidi-font-family:Arial;mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA;"></SPAN></SPAN>&nbsp;</P>