ALTER TABLE statements showing up in my stored procedures
in [Programming SQL]
|
From: Sam on 11 Aug 2010 18:42 Hi, Recently, I noticed some ALTER TABLE statements in my stored procedures. I have no idea where they came from. The only thing I can think of is that I do generate scripts to keep my local copy of the database where I develop my application and the production server in sync. I think these statements started showing up after I upgraded to SQL Server 2008 R2 (full version on production server and Express on the local machine). I just tried removing them from one of my stored procedures and they came right back. I then deleted the stored procedure and recreated it without these ALTER TABLE statements and when I opened the newly created stored procedure, they were there again. Clearly there's something I don't understand going on here. Here's one example -- the stored procedure is one that is created by ASP.NET Membership module. I have similar ALTER TABLE statements in my stored procedures. Here's the scripts for the AnyDataInTables strored procedure: /****** Object: StoredProcedure [dbo].[aspnet_AnyDataInTables] Script Date: 08/11/2010 18:40:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[aspnet_AnyDataInTables] @TablesToCheck int AS BEGIN -- Check Membership table if (@TablesToCheck & 1) is set IF ((@TablesToCheck & 1) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V')))) BEGIN IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership)) BEGIN SELECT N'aspnet_Membership' RETURN END END -- Check aspnet_Roles table if (@TablesToCheck & 2) is set IF ((@TablesToCheck & 2) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Roles') AND (type = 'V'))) ) BEGIN IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles)) BEGIN SELECT N'aspnet_Roles' RETURN END END -- Check aspnet_Profile table if (@TablesToCheck & 4) is set IF ((@TablesToCheck & 4) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) ) BEGIN IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile)) BEGIN SELECT N'aspnet_Profile' RETURN END END -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set IF ((@TablesToCheck & 8) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) ) BEGIN IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser)) BEGIN SELECT N'aspnet_PersonalizationPerUser' RETURN END END -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set IF ((@TablesToCheck & 16) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_WebEvent_LogEvent') AND (type = 'P'))) ) BEGIN IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events)) BEGIN SELECT N'aspnet_WebEvent_Events' RETURN END END -- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set IF ((@TablesToCheck & 1) <> 0 AND (@TablesToCheck & 2) <> 0 AND (@TablesToCheck & 4) <> 0 AND (@TablesToCheck & 8) <> 0 AND (@TablesToCheck & 32) <> 0 AND (@TablesToCheck & 128) <> 0 AND (@TablesToCheck & 256) <> 0 AND (@TablesToCheck & 512) <> 0 AND (@TablesToCheck & 1024) <> 0) BEGIN IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users)) BEGIN SELECT N'aspnet_Users' RETURN END IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications)) BEGIN SELECT N'aspnet_Applications' RETURN END END END GO ALTER TABLE [dbo].[aspnet_Applications] ADD CONSTRAINT [DF__aspnet_Ap__Appli__08EA5793] DEFAULT (newid()) FOR [ApplicationId] GO ALTER TABLE [dbo].[aspnet_Users] WITH CHECK ADD CONSTRAINT [FK__aspnet_Us__Appli__0DAF0CB0] FOREIGN KEY([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId]) GO ALTER TABLE [dbo].[aspnet_Users] CHECK CONSTRAINT [FK__aspnet_Us__Appli__0DAF0CB0] GO ALTER TABLE [dbo].[aspnet_Users] ADD CONSTRAINT [DF__aspnet_Us__UserI__0EA330E9] DEFAULT (newid()) FOR [UserId] GO ALTER TABLE [dbo].[aspnet_Users] ADD CONSTRAINT [DF__aspnet_Us__Mobil__0F975522] DEFAULT (NULL) FOR [MobileAlias] GO ALTER TABLE [dbo].[aspnet_Users] ADD CONSTRAINT [DF__aspnet_Us__IsAno__108B795B] DEFAULT ((0)) FOR [IsAnonymous] GO ALTER TABLE [dbo].[aspnet_Membership] WITH CHECK ADD CONSTRAINT [FK__aspnet_Me__Appli__21B6055D] FOREIGN KEY([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId]) GO ALTER TABLE [dbo].[aspnet_Membership] CHECK CONSTRAINT [FK__aspnet_Me__Appli__21B6055D] GO ALTER TABLE [dbo].[aspnet_Membership] WITH CHECK ADD CONSTRAINT [FK__aspnet_Me__UserI__22AA2996] FOREIGN KEY([UserId]) REFERENCES [dbo].[aspnet_Users] ([UserId]) GO ALTER TABLE [dbo].[aspnet_Membership] CHECK CONSTRAINT [FK__aspnet_Me__UserI__22AA2996] GO ALTER TABLE [dbo].[aspnet_Membership] ADD CONSTRAINT [DF__aspnet_Me__Passw__239E4DCF] DEFAULT ((0)) FOR [PasswordFormat] GO ALTER TABLE [dbo].[aspnet_Paths] WITH CHECK ADD CONSTRAINT [FK__aspnet_Pa__Appli__5AEE82B9] FOREIGN KEY([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId]) GO ALTER TABLE [dbo].[aspnet_Paths] CHECK CONSTRAINT [FK__aspnet_Pa__Appli__5AEE82B9] GO ALTER TABLE [dbo].[aspnet_Paths] ADD CONSTRAINT [DF__aspnet_Pa__PathI__5BE2A6F2] DEFAULT (newid()) FOR [PathId] GO ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] WITH CHECK ADD CONSTRAINT [FK__aspnet_Pe__PathI__68487DD7] FOREIGN KEY([PathId]) REFERENCES [dbo].[aspnet_Paths] ([PathId]) GO ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] CHECK CONSTRAINT [FK__aspnet_Pe__PathI__68487DD7] GO ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] WITH CHECK ADD CONSTRAINT [FK__aspnet_Pe__UserI__693CA210] FOREIGN KEY([UserId]) REFERENCES [dbo].[aspnet_Users] ([UserId]) GO ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] CHECK CONSTRAINT [FK__aspnet_Pe__UserI__693CA210] GO ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] ADD CONSTRAINT [DF__aspnet_Perso__Id__6754599E] DEFAULT (newid()) FOR [Id] GO ALTER TABLE [dbo].[aspnet_Profile] WITH CHECK ADD CONSTRAINT [FK__aspnet_Pr__UserI__38996AB5] FOREIGN KEY([UserId]) REFERENCES [dbo].[aspnet_Users] ([UserId]) GO ALTER TABLE [dbo].[aspnet_Profile] CHECK CONSTRAINT [FK__aspnet_Pr__UserI__38996AB5] GO ALTER TABLE [dbo].[aspnet_Roles] WITH CHECK ADD CONSTRAINT [FK__aspnet_Ro__Appli__440B1D61] FOREIGN KEY([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId]) GO ALTER TABLE [dbo].[aspnet_Roles] CHECK CONSTRAINT [FK__aspnet_Ro__Appli__440B1D61] GO ALTER TABLE [dbo].[aspnet_Roles] ADD CONSTRAINT [DF__aspnet_Ro__RoleI__44FF419A] DEFAULT (newid()) FOR [RoleId] -- Thanks, Sam
From: Erland Sommarskog on 12 Aug 2010 03:39 Sam (Sam(a)discussions.microsoft.com) writes: > Recently, I noticed some ALTER TABLE statements in my stored procedures. I > have no idea where they came from. I cannot see any ALTER TABLE in that procedure. The procedure is followed by a bunch of ALTER TABLE statements, but that's another story. They are no part of the procedure. > The only thing I can think of is that I do generate scripts to keep my > local copy of the database where I develop my application and the > production server in sync. The correct way is to keep your code under version control, and consider what you have in the database as binaries. You would not get the idea of generating your C# code by disassembling the assemblies, would you? -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Sam on 12 Aug 2010 17:15 Erland, I realize that they're not part of the stored procedure because they're after the GO statement. My question is why are they showing up? To further clarify, if I create a brand new stored procedure and open it immediately after creating it, I see these ALTER statements. Possibly SSMS is putting them there when I open them up. Any idea if this is some setting somewhere? -- Thanks, Sam "Erland Sommarskog" wrote: > Sam (Sam(a)discussions.microsoft.com) writes: > > Recently, I noticed some ALTER TABLE statements in my stored procedures. I > > have no idea where they came from. > > I cannot see any ALTER TABLE in that procedure. The procedure is followed by > a bunch of ALTER TABLE statements, but that's another story. They are no > part of the procedure. > > > The only thing I can think of is that I do generate scripts to keep my > > local copy of the database where I develop my application and the > > production server in sync. > > The correct way is to keep your code under version control, and consider > what you have in the database as binaries. You would not get the idea of > generating your C# code by disassembling the assemblies, would you? > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > . >
From: Sam on 12 Aug 2010 17:46 I figured out why this was happening. It was due to a setting in SSMS i.e. Tools > Options > SQL Server Object Explorer > Scripting --> "Generate script for dependent objects set to True" I always generate scripts for updating my production server and a couple of weeks ago, decided to set the "Scripting Options" globally so that I don't have to go through each and every item everytime I want to generate scripts. That's when I set the "Generate script for dependent objects" to True. When I set it fale, all these ALTER TABLE statements disappeared. Hopefully, this post will help someone in the future. -- Thanks, Sam "Erland Sommarskog" wrote: > Sam (Sam(a)discussions.microsoft.com) writes: > > Recently, I noticed some ALTER TABLE statements in my stored procedures. I > > have no idea where they came from. > > I cannot see any ALTER TABLE in that procedure. The procedure is followed by > a bunch of ALTER TABLE statements, but that's another story. They are no > part of the procedure. > > > The only thing I can think of is that I do generate scripts to keep my > > local copy of the database where I develop my application and the > > production server in sync. > > The correct way is to keep your code under version control, and consider > what you have in the database as binaries. You would not get the idea of > generating your C# code by disassembling the assemblies, would you? > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > . >
|
Pages: 1 Prev: how to dateadd with bigint Next: Trigger that acts on either the NEW pk or the existing pk |