You should script all your database changes and then push the scripts out to other locations to be executed.
As soon as you make changes to database objects from inside Management Studio you lose the ability to re-create them easily on another copy of the database.
This script creates a table
Code:
Use Health
Go
Drop Table AWCDocuments_T
Go
Create Table AWCDocuments_T
(DocId int not null identity
,DocFolder varchar(100) not null
,DocStatus varchar(10) not null
,DocName varchar(1000) not null
,StoredFileName varchar(1000) not null
,Username varchar(1000) not null
,Notes varchar(1000) not null
,TDate datetime
,Constraint PKDocuments
Primary Key (DocId)
)
Go
Select * From AWCDocuments_T
This script adds a new field at the "second-to-last" position in the record - we always have our TDATE fields as the right-most field. It does this change with data in the table.
Code:
Use Funds
Go
EXEC sp_rename 'PenNote_T.TDate','MisMatch','COLUMN'
Go
Alter Table PenNote_T Add TDate datetime null
Go
Update PenNote_T Set TDate=MisMatch
Go
Update PenNote_T Set MisMatch=null
Alter Table PenNote_T Alter Column MisMatch varchar(1) null
Update PenNote_T Set MisMatch=''
This script pushes out a function
Code:
Use Funds
Go
Drop Function dbo.GetPenNotes_Mismatch_F
Go
Create Function dbo.GetPenNotes_Mismatch_F (@MasId int)
Returns varchar(1000)
As
Begin
Declare @RV varchar(1000)
Select @RV=IsNull(@RV+', ','')+Notes
From PenNote_T
Where MasId=@MasId and IsNull(MisMatch,'')='Y'
Return @RV
End
Go
These scripts get saved just like source-code to your .Net app. And you can put them under source-control - a real benefit for tracking changes.
Plus you can search them very easily and find references to tables and fields - making future updates and enhancements easier to do.
Reply With Quote
