SQL: SELECT * FROM INFORMATION_SCHEMA.ROUTINES

query
  CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH CHARACTER_SET_CATALOG CHARACTER_SET_NAME CHARACTER_SET_SCHEMA COLLATION_CATALOG COLLATION_NAME COLLATION_SCHEMA CREATED DATA_TYPE DATETIME_PRECISION DTD_IDENTIFIER EXTERNAL_LANGUAGE EXTERNAL_NAME INTERVAL_PRECISION INTERVAL_TYPE IS_DETERMINISTIC IS_IMPLICITLY_INVOCABLE IS_NULL_CALL IS_USER_DEFINED_CAST LAST_ALTERED MAXIMUM_CARDINALITY MAX_DYNAMIC_RESULT_SETS MODULE_CATALOG MODULE_NAME MODULE_SCHEMA NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE PARAMETER_STYLE ROUTINE_BODY ROUTINE_CATALOG ROUTINE_DEFINITION ROUTINE_NAME ROUTINE_SCHEMA ROUTINE_TYPE SCHEMA_LEVEL_ROUTINE SCOPE_CATALOG SCOPE_NAME SCOPE_SCHEMA SPECIFIC_CATALOG SPECIFIC_NAME SPECIFIC_SCHEMA SQL_DATA_ACCESS SQL_PATH TYPE_UDT_CATALOG TYPE_UDT_NAME TYPE_UDT_SCHEMA UDT_CATALOG UDT_NAME UDT_SCHEMA
1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-15 13:54:51.42 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-15 13:54:51.42 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb CREATE PROCEDURE GetUserDetails (@UserName varchar(50)) AS SELECT * FROM vwUser WHERE UserName = @UserName GetUserDetails RSilverbergS PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb GetUserDetails RSilverbergS MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
2 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-15 13:54:51.95 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-15 13:54:51.95 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb CREATE PROCEDURE GetUsers AS SELECT * FROM vwUser ORDER BY UserName GetUsers RSilverbergS PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb GetUsers RSilverbergS MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
3 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:16.577 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:16.577 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_checkinobject_u @chObjectType char(4), @vchObjectName nvarchar(255), @vchComment nvarchar(255)='', @vchLoginName nvarchar(255), @vchPassword nvarchar(255)='', @iVCSFlags int = 0, @iActionFlag int = 0, /* 0 => AddFile, 1 => CheckIn */ @txStream1 Text = '', /* There is a bug that if items are NULL they do not pass to OLE servers */ @txStream2 Text = '', @txStream3 Text = '' as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId = 0 declare @VSSGUID nvarchar(100) select @VSSGUID = N'SQLVersionControl.VCS_SQL' declare @iPropertyObjectId int select @iPropertyObjectId = 0 select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchProjectName nvarchar(255) declare @vchSourceSafeINI nvarchar(255) declare @vchServerName nvarchar(255) declare @vchDatabaseName nvarchar(255) exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSProject', @vchProjectName OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT if @chObjectType = 'PROC' begin if @iActionFlag = 1 begin /* Procedure Can have up to three streams Drop Stream, Create Stream, GRANT stream */ begin tran compile_all /* try to compile the streams */ exec (@txStream1) if @@error <> 0 GOTO E_Compile_Fail exec (@txStream2) if @@error <> 0 GOTO E_Compile_Fail exec (@txStream3) if @@error <> 0 GOTO E_Compile_Fail end exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError if @iActionFlag = 1 begin exec @iReturn = sp_OAMethod @iObjectId, N'CheckIn_StoredProcedure', NULL, @sProjectName = @vchProjectName, @sSourceSafeINI = @vchSourceSafeINI, @sServerName = @vchServerName, @sDatabaseName = @vchDatabaseName, @sObjectName = @vchObjectName, @sComment = @vchComment, @sLoginName = @vchLoginName, @sPassword = @vchPassword, @iVCSFlags = @iVCSFlags, @iActionFlag = @iActionFlag, @sStream = @txStream2 end else begin declare @iStreamObjectId int declare @iReturnValue int exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT if @iReturn <> 0 GOTO E_OAError select colid, text into #ProcLines from syscomments where id = object_id(@vchObjectName) order by colid declare @iCurProcLine int declare @iProcLines int select @iCurProcLine = 1 select @iProcLines = (select count(*) from #ProcLines) while @iCurProcLine <= @iProcLines begin declare @pos int select @pos = 1 declare @iCurLineSize int select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine)) while @pos <= @iCurLineSize dt_checkinobject_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_checkinobject_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
4 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:16.933 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:16.933 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_checkoutobject_u @chObjectType char(4), @vchObjectName nvarchar(255), @vchComment nvarchar(255), @vchLoginName nvarchar(255), @vchPassword nvarchar(255), @iVCSFlags int = 0, @iActionFlag int = 0/* 0 => Checkout, 1 => GetLatest, 2 => UndoCheckOut */ as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId =0 declare @VSSGUID nvarchar(100) select @VSSGUID = N'SQLVersionControl.VCS_SQL' declare @iReturnValue int select @iReturnValue = 0 declare @vchTempText nvarchar(255) /* this is for our strings */ declare @iStreamObjectId int select @iStreamObjectId = 0 declare @iPropertyObjectId int select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchProjectName nvarchar(255) declare @vchSourceSafeINI nvarchar(255) declare @vchServerName nvarchar(255) declare @vchDatabaseName nvarchar(255) exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSProject', @vchProjectName OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT if @chObjectType = 'PROC' begin /* Procedure Can have up to three streams Drop Stream, Create Stream, GRANT stream */ exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAMethod @iObjectId, N'CheckOut_StoredProcedure', NULL, @sProjectName = @vchProjectName, @sSourceSafeINI = @vchSourceSafeINI, @sObjectName = @vchObjectName, @sServerName = @vchServerName, @sDatabaseName = @vchDatabaseName, @sComment = @vchComment, @sLoginName = @vchLoginName, @sPassword = @vchPassword, @iVCSFlags = @iVCSFlags, @iActionFlag = @iActionFlag if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT if @iReturn <> 0 GOTO E_OAError create table #commenttext (id int identity, sourcecode nvarchar(255)) select @vchTempText = N'STUB' while @vchTempText IS NOT NULL begin exec @iReturn = sp_OAMethod @iStreamObjectId, N'GetStream', @iReturnValue OUT, @vchTempText OUT if @iReturn <> 0 GOTO E_OAError if (@vchTempText IS NOT NULL) insert into #commenttext (sourcecode) select @vchTempText end select N'VCS'=sourcecode from #commenttext order by id select N'SQL'=text from syscomments where id = object_id(@vchObjectName) order by colid end CleanUp: return E_OAError: exec dbo.dt_displayoaerror_u @iObjectId, @iReturn GOTO CleanUp dt_checkoutobject_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_checkoutobject_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
5 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:17.09 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:17.09 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb CREATE PROCEDURE dbo.dt_displayoaerror_u @iObject int, @iresult int as set nocount on declare @vchOutput nvarchar(255) declare @hr int declare @vchSource nvarchar(255) declare @vchDescription nvarchar(255) exec @hr = sp_OAGetErrorInfo @iObject, @vchSource OUT, @vchDescription OUT select @vchOutput = @vchSource + ': ' + @vchDescription raiserror (@vchOutput,16,-1) return dt_displayoaerror_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_displayoaerror_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
6 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:17.247 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:17.247 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create procedure dbo.dt_getpropertiesbyid_vcs_u @id int, @property varchar(64), @value nvarchar(255) = NULL OUT as set nocount on select @value = ( select uvalue from dbo.dtproperties where @id=objectid and @property=property ) dt_getpropertiesbyid_vcs_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_getpropertiesbyid_vcs_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
7 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:18.25 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:18.25 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_isundersourcecontrol_u @vchLoginName nvarchar(255) = '', @vchPassword nvarchar(255) = '', @iWhoToo int = 0 /* 0 => Just check project; 1 => get list of objs */ as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId = 0 declare @VSSGUID nvarchar(100) select @VSSGUID = N'SQLVersionControl.VCS_SQL' declare @iReturnValue int select @iReturnValue = 0 declare @iStreamObjectId int select @iStreamObjectId = 0 declare @vchTempText nvarchar(255) declare @iPropertyObjectId int select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchProjectName nvarchar(255) declare @vchSourceSafeINI nvarchar(255) declare @vchServerName nvarchar(255) declare @vchDatabaseName nvarchar(255) exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSProject', @vchProjectName OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT if (@vchProjectName IS NULL) or (@vchSourceSafeINI IS NULL) or (@vchServerName IS NULL) or (@vchDatabaseName IS NULL) begin RAISERROR(N'Not Under Source Control',16,-1) return end if @iWhoToo = 1 begin /* Get List of Procs in the project */ exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAMethod @iObjectId, N'GetListOfObjects', NULL, @vchProjectName, @vchSourceSafeINI, @vchServerName, @vchDatabaseName, @vchLoginName, @vchPassword if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT if @iReturn <> 0 GOTO E_OAError create table #ObjectList (id int identity, vchObjectlist nvarchar(255)) select @vchTempText = N'STUB' while @vchTempText IS NOT NULL begin exec @iReturn = sp_OAMethod @iStreamObjectId, N'GetStream', @iReturnValue OUT, @vchTempText OUT if @iReturn <> 0 GOTO E_OAError if (@vchTempText IS NOT NULL) insert into #ObjectList (vchObjectlist ) select @vchTempText end select vchObjectlist from #ObjectList order by id end CleanUp: return E_OAError: exec dbo.dt_displayoaerror_u @iObjectId, @iReturn goto CleanUp dt_isundersourcecontrol_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_isundersourcecontrol_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
8 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:19.013 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:19.013 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_validateloginparams_u @vchLoginName nvarchar(255), @vchPassword nvarchar(255) as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId =0 declare @VSSGUID nvarchar(100) select @VSSGUID = N'SQLVersionControl.VCS_SQL' declare @iPropertyObjectId int select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchSourceSafeINI nvarchar(255) exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAMethod @iObjectId, N'ValidateLoginParams', NULL, @sSourceSafeINI = @vchSourceSafeINI, @sLoginName = @vchLoginName, @sPassword = @vchPassword if @iReturn <> 0 GOTO E_OAError CleanUp: return E_OAError: exec dbo.dt_displayoaerror_u @iObjectId, @iReturn GOTO CleanUp dt_validateloginparams_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_validateloginparams_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
9 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:19.857 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:19.857 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_whocheckedout_u @chObjectType char(4), @vchObjectName nvarchar(255), @vchLoginName nvarchar(255), @vchPassword nvarchar(255) as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId =0 declare @VSSGUID nvarchar(100) select @VSSGUID = N'SQLVersionControl.VCS_SQL' declare @iPropertyObjectId int select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchProjectName nvarchar(255) declare @vchSourceSafeINI nvarchar(255) declare @vchServerName nvarchar(255) declare @vchDatabaseName nvarchar(255) exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSProject', @vchProjectName OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT if @chObjectType = 'PROC' begin exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError declare @vchReturnValue nvarchar(255) select @vchReturnValue = '' exec @iReturn = sp_OAMethod @iObjectId, N'WhoCheckedOut', @vchReturnValue OUT, @sProjectName = @vchProjectName, @sSourceSafeINI = @vchSourceSafeINI, @sObjectName = @vchObjectName, @sServerName = @vchServerName, @sDatabaseName = @vchDatabaseName, @sLoginName = @vchLoginName, @sPassword = @vchPassword if @iReturn <> 0 GOTO E_OAError select @vchReturnValue end CleanUp: return E_OAError: exec dbo.dt_displayoaerror_u @iObjectId, @iReturn GOTO CleanUp dt_whocheckedout_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_whocheckedout_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
10 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-15 13:54:49.293 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-15 13:54:49.293 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb CREATE PROCEDURE [AddUser] (@UserName [varchar](50), @Email [varchar](100), @Phone [varchar](50), @Password [varchar](50), @UserTypeID [int] = 1, @ActiveFlag [bit] = 0, @UserDetails text = NULL, @StartDate datetime = GETDATE, @WalletContents money) AS INSERT INTO [User] ( [UserName], [Email], [Phone], [Password], [UserTypeID], [ActiveFlag], [UserDetails], [StartDate], [WalletContents]) VALUES (@UserName, @Email, @Phone, @Password, @UserTypeID, @ActiveFlag, @UserDetails, @StartDate, @WalletContents) SELECT @@IDENTITY AS UserID AddUser RSilverbergS PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb AddUser RSilverbergS MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
11 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-15 13:54:50.34 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-15 13:54:50.34 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb CREATE PROCEDURE [DeleteUser] (@UserID [int]) AS DELETE FROM [User] WHERE [UserID] = @UserID DeleteUser RSilverbergS PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb DeleteUser RSilverbergS MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
12 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-15 13:54:52.48 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-15 13:54:52.48 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb CREATE PROCEDURE GetUserTypeDetails (@UserTypeID int) AS SELECT UserTypeID, UserTypeDesc, ActiveFlag FROM UserType WHERE UserTypeID = @UserTypeID GetUserTypeDetails RSilverbergS PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb GetUserTypeDetails RSilverbergS MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
13 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:06.34 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:06.34 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** Generate an ansi name that is unique in the dtproperties.value column */ create procedure dbo.dt_generateansiname(@name varchar(255) output) as declare @prologue varchar(20) declare @indexstring varchar(20) declare @index integer set @prologue = 'MSDT-A-' set @index = 1 while 1 = 1 begin set @indexstring = cast(@index as varchar(20)) set @name = @prologue + @indexstring if not exists (select value from dtproperties where value = @name) break set @index = @index + 1 if (@index = 10000) goto TooMany end Leave: return TooMany: set @name = 'DIAGRAM' goto Leave dt_generateansiname dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_generateansiname dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
14 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:06.497 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:06.497 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** Add an object to the dtproperties table */ create procedure dbo.dt_adduserobject as set nocount on /* ** Create the user object if it does not exist already */ begin transaction insert dbo.dtproperties (property) VALUES ('DtgSchemaOBJECT') update dbo.dtproperties set objectid=@@identity where id=@@identity and property='DtgSchemaOBJECT' commit return @@identity dt_adduserobject dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_adduserobject dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
15 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:06.653 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:06.653 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** If the property already exists, reset the value; otherwise add property ** id -- the id in sysobjects of the object ** property -- the name of the property ** value -- the text value of the property ** lvalue -- the binary value of the property (image) */ create procedure dbo.dt_setpropertybyid @id int, @property varchar(64), @value varchar(255), @lvalue image as set nocount on declare @uvalue nvarchar(255) set @uvalue = convert(nvarchar(255), @value) if exists (select * from dbo.dtproperties where objectid=@id and property=@property) begin -- -- bump the version count for this row as we update it -- update dbo.dtproperties set value=@value, uvalue=@uvalue, lvalue=@lvalue, version=version+1 where objectid=@id and property=@property end else begin -- -- version count is auto-set to 0 on initial insert -- insert dbo.dtproperties (property, objectid, value, uvalue, lvalue) values (@property, @id, @value, @uvalue, @lvalue) end dt_setpropertybyid dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_setpropertybyid dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
16 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:06.793 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:06.793 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** Retrieve the owner object(s) of a given property */ create procedure dbo.dt_getobjwithprop @property varchar(30), @value varchar(255) as set nocount on if (@property is null) or (@property = '') begin raiserror('Must specify a property name.',-1,-1) return (1) end if (@value is null) select objectid id from dbo.dtproperties where property=@property else select objectid id from dbo.dtproperties where property=@property and value=@value dt_getobjwithprop dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_getobjwithprop dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
17 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:06.95 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:06.95 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** Retrieve properties by id's ** ** dt_getproperties objid, null or '' -- retrieve all properties of the object itself ** dt_getproperties objid, property -- retrieve the property specified */ create procedure dbo.dt_getpropertiesbyid @id int, @property varchar(64) as set nocount on if (@property is null) or (@property = '') select property, version, value, lvalue from dbo.dtproperties where @id=objectid else select property, version, value, lvalue from dbo.dtproperties where @id=objectid and @property=property dt_getpropertiesbyid dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_getpropertiesbyid dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
18 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:07.78 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:07.78 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** If the property already exists, reset the value; otherwise add property ** id -- the id in sysobjects of the object ** property -- the name of the property ** uvalue -- the text value of the property ** lvalue -- the binary value of the property (image) */ create procedure dbo.dt_setpropertybyid_u @id int, @property varchar(64), @uvalue nvarchar(255), @lvalue image as set nocount on -- -- If we are writing the name property, find the ansi equivalent. -- If there is no lossless translation, generate an ansi name. -- declare @avalue varchar(255) set @avalue = null if (@uvalue is not null) begin if (convert(nvarchar(255), convert(varchar(255), @uvalue)) = @uvalue) begin set @avalue = convert(varchar(255), @uvalue) end else begin if 'DtgSchemaNAME' = @property begin exec dbo.dt_generateansiname @avalue output end end end if exists (select * from dbo.dtproperties where objectid=@id and property=@property) begin -- -- bump the version count for this row as we update it -- update dbo.dtproperties set value=@avalue, uvalue=@uvalue, lvalue=@lvalue, version=version+1 where objectid=@id and property=@property end else begin -- -- version count is auto-set to 0 on initial insert -- insert dbo.dtproperties (property, objectid, value, uvalue, lvalue) values (@property, @id, @avalue, @uvalue, @lvalue) end dt_setpropertybyid_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_setpropertybyid_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
19 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:08.013 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:08.013 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** Retrieve the owner object(s) of a given property */ create procedure dbo.dt_getobjwithprop_u @property varchar(30), @uvalue nvarchar(255) as set nocount on if (@property is null) or (@property = '') begin raiserror('Must specify a property name.',-1,-1) return (1) end if (@uvalue is null) select objectid id from dbo.dtproperties where property=@property else select objectid id from dbo.dtproperties where property=@property and uvalue=@uvalue dt_getobjwithprop_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_getobjwithprop_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
20 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:08.183 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:08.183 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** Retrieve properties by id's ** ** dt_getproperties objid, null or '' -- retrieve all properties of the object itself ** dt_getproperties objid, property -- retrieve the property specified */ create procedure dbo.dt_getpropertiesbyid_u @id int, @property varchar(64) as set nocount on if (@property is null) or (@property = '') select property, version, uvalue, lvalue from dbo.dtproperties where @id=objectid else select property, version, uvalue, lvalue from dbo.dtproperties where @id=objectid and @property=property dt_getpropertiesbyid_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_getpropertiesbyid_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
21 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:08.373 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:08.373 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** Drop an object from the dbo.dtproperties table */ create procedure dbo.dt_dropuserobjectbyid @id int as set nocount on delete from dbo.dtproperties where objectid=@id dt_dropuserobjectbyid dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_dropuserobjectbyid dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
22 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:08.827 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:08.827 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** Drop one or all the associated properties of an object or an attribute ** ** dt_dropproperties objid, null or '' -- drop all properties of the object itself ** dt_dropproperties objid, property -- drop the property */ create procedure dbo.dt_droppropertiesbyid @id int, @property varchar(64) as set nocount on if (@property is null) or (@property = '') delete from dbo.dtproperties where objectid=@id else delete from dbo.dtproperties where objectid=@id and property=@property dt_droppropertiesbyid dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_droppropertiesbyid dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
23 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:09.09 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:09.09 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb /* ** This procedure returns the version number of the stored ** procedures used by the Microsoft Visual Database Tools. ** Current version is 7.0.00. */ create procedure dbo.dt_verstamp006 as select 7000 dt_verstamp006 dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_verstamp006 dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
24 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:09.373 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:09.373 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create procedure dbo.dt_getpropertiesbyid_vcs @id int, @property varchar(64), @value varchar(255) = NULL OUT as set nocount on select @value = ( select value from dbo.dtproperties where @id=objectid and @property=property ) dt_getpropertiesbyid_vcs dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_getpropertiesbyid_vcs dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
25 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:09.543 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:09.543 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb CREATE PROCEDURE dbo.dt_displayoaerror @iObject int, @iresult int as set nocount on declare @vchOutput varchar(255) declare @hr int declare @vchSource varchar(255) declare @vchDescription varchar(255) exec @hr = sp_OAGetErrorInfo @iObject, @vchSource OUT, @vchDescription OUT select @vchOutput = @vchSource + ': ' + @vchDescription raiserror (@vchOutput,16,-1) return dt_displayoaerror dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_displayoaerror dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
26 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:09.747 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:09.747 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create procedure dbo.dt_adduserobject_vcs @vchProperty varchar(64) as set nocount on declare @iReturn int /* ** Create the user object if it does not exist already */ begin transaction select @iReturn = objectid from dbo.dtproperties where property = @vchProperty if @iReturn IS NULL begin insert dbo.dtproperties (property) VALUES (@vchProperty) update dbo.dtproperties set objectid=@@identity where id=@@identity and property=@vchProperty select @iReturn = @@identity end commit return @iReturn dt_adduserobject_vcs dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_adduserobject_vcs dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
27 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:12.153 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:12.153 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_addtosourcecontrol @vchSourceSafeINI varchar(255) = '', @vchProjectName varchar(255) ='', @vchComment varchar(255) ='', @vchLoginName varchar(255) ='', @vchPassword varchar(255) ='' as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId = 0 declare @iStreamObjectId int select @iStreamObjectId = 0 declare @VSSGUID varchar(100) select @VSSGUID = 'SQLVersionControl.VCS_SQL' declare @vchDatabaseName varchar(255) select @vchDatabaseName = db_name() declare @iReturnValue int select @iReturnValue = 0 declare @iPropertyObjectId int declare @vchParentId varchar(255) declare @iObjectCount int select @iObjectCount = 0 exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError /* Create Project in SS */ exec @iReturn = sp_OAMethod @iObjectId, 'AddProjectToSourceSafe', NULL, @vchSourceSafeINI, @vchProjectName output, @@SERVERNAME, @vchDatabaseName, @vchLoginName, @vchPassword, @vchComment if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT if @iReturn <> 0 GOTO E_OAError /* Set Database Properties */ begin tran SetProperties /* add high level object */ exec @iPropertyObjectId = dbo.dt_adduserobject_vcs 'VCSProjectID' select @vchParentId = CONVERT(varchar(255),@iPropertyObjectId) exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSProjectID', @vchParentId , NULL exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSProject' , @vchProjectName , NULL exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSourceSafeINI' , @vchSourceSafeINI , NULL exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSQLServer', @@SERVERNAME, NULL exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName, NULL if @@error <> 0 GOTO E_General_Error commit tran SetProperties declare cursorProcNames cursor for select convert(varchar(255), name) from sysobjects where type = 'P' and name not like 'dt_%' open cursorProcNames while 1 = 1 begin declare @vchProcName varchar(255) fetch next from cursorProcNames into @vchProcName if @@fetch_status <> 0 break select colid, text into #ProcLines from syscomments where id = object_id(@vchProcName) order by colid declare @iCurProcLine int declare @iProcLines int select @iCurProcLine = 1 select @iProcLines = (select count(*) from #ProcLines) while @iCurProcLine <= @iProcLines begin declare @pos int select @pos = 1 declare @iCurLineSize int select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine)) while @pos <= @iCurLineSize begin declare @vchProcLinePiece varchar(255) select @vchProcLinePiece = convert(varchar(255), substring((select text from #ProcLines where colid = @iCurProcLine), @pos, 255 )) exec @iReturn = sp_OAMethod @iStreamObjectId, 'AddStream', @iReturnValue OUT, @vchProcLinePiece if @iReturn <> 0 GOTO E_OAError select @pos = @pos + 255 end select @iCurProcLine = @iCurProcLine + 1 end drop table #ProcLines exec @iReturn = sp_OAMethod @iObjectId, 'CheckIn_StoredProcedure', dt_addtosourcecontrol dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_addtosourcecontrol dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
28 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:12.67 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:12.67 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_checkinobject @chObjectType char(4), @vchObjectName varchar(255), @vchComment varchar(255)='', @vchLoginName varchar(255), @vchPassword varchar(255)='', @iVCSFlags int = 0, @iActionFlag int = 0, /* 0 => AddFile, 1 => CheckIn */ @txStream1 Text = '', /* There is a bug that if items are NULL they do not pass to OLE servers */ @txStream2 Text = '', @txStream3 Text = '' as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId = 0 declare @VSSGUID varchar(100) select @VSSGUID = 'SQLVersionControl.VCS_SQL' declare @iPropertyObjectId int select @iPropertyObjectId = 0 select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchProjectName varchar(255) declare @vchSourceSafeINI varchar(255) declare @vchServerName varchar(255) declare @vchDatabaseName varchar(255) exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSProject', @vchProjectName OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT if @chObjectType = 'PROC' begin if @iActionFlag = 1 begin /* Procedure Can have up to three streams Drop Stream, Create Stream, GRANT stream */ begin tran compile_all /* try to compile the streams */ exec (@txStream1) if @@error <> 0 GOTO E_Compile_Fail exec (@txStream2) if @@error <> 0 GOTO E_Compile_Fail exec (@txStream3) if @@error <> 0 GOTO E_Compile_Fail end exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError if @iActionFlag = 1 begin exec @iReturn = sp_OAMethod @iObjectId, 'CheckIn_StoredProcedure', NULL, @sProjectName = @vchProjectName, @sSourceSafeINI = @vchSourceSafeINI, @sServerName = @vchServerName, @sDatabaseName = @vchDatabaseName, @sObjectName = @vchObjectName, @sComment = @vchComment, @sLoginName = @vchLoginName, @sPassword = @vchPassword, @iVCSFlags = @iVCSFlags, @iActionFlag = @iActionFlag, @sStream = @txStream2 end else begin declare @iStreamObjectId int declare @iReturnValue int exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT if @iReturn <> 0 GOTO E_OAError select colid, text into #ProcLines from syscomments where id = object_id(@vchObjectName) order by colid declare @iCurProcLine int declare @iProcLines int select @iCurProcLine = 1 select @iProcLines = (select count(*) from #ProcLines) while @iCurProcLine <= @iProcLines begin declare @pos int select @pos = 1 declare @iCurLineSize int select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine)) while @pos <= @iCurLineSize begin dt_checkinobject dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_checkinobject dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
29 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:13.013 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:13.013 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_checkoutobject @chObjectType char(4), @vchObjectName varchar(255), @vchComment varchar(255), @vchLoginName varchar(255), @vchPassword varchar(255), @iVCSFlags int = 0, @iActionFlag int = 0/* 0 => Checkout, 1 => GetLatest, 2 => UndoCheckOut */ as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId =0 declare @VSSGUID varchar(100) select @VSSGUID = 'SQLVersionControl.VCS_SQL' declare @iReturnValue int select @iReturnValue = 0 declare @vchTempText varchar(255) /* this is for our strings */ declare @iStreamObjectId int select @iStreamObjectId = 0 declare @iPropertyObjectId int select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchProjectName varchar(255) declare @vchSourceSafeINI varchar(255) declare @vchServerName varchar(255) declare @vchDatabaseName varchar(255) exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSProject', @vchProjectName OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT if @chObjectType = 'PROC' begin /* Procedure Can have up to three streams Drop Stream, Create Stream, GRANT stream */ exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAMethod @iObjectId, 'CheckOut_StoredProcedure', NULL, @sProjectName = @vchProjectName, @sSourceSafeINI = @vchSourceSafeINI, @sObjectName = @vchObjectName, @sServerName = @vchServerName, @sDatabaseName = @vchDatabaseName, @sComment = @vchComment, @sLoginName = @vchLoginName, @sPassword = @vchPassword, @iVCSFlags = @iVCSFlags, @iActionFlag = @iActionFlag if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT if @iReturn <> 0 GOTO E_OAError create table #commenttext (id int identity, sourcecode varchar(255)) select @vchTempText = 'STUB' while @vchTempText IS NOT NULL begin exec @iReturn = sp_OAMethod @iStreamObjectId, 'GetStream', @iReturnValue OUT, @vchTempText OUT if @iReturn <> 0 GOTO E_OAError if (@vchTempText IS NOT NULL) insert into #commenttext (sourcecode) select @vchTempText end select 'VCS'=sourcecode from #commenttext order by id select 'SQL'=text from syscomments where id = object_id(@vchObjectName) order by colid end CleanUp: return E_OAError: exec dbo.dt_displayoaerror @iObjectId, @iReturn GOTO CleanUp dt_checkoutobject dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_checkoutobject dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
30 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:14.42 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:14.42 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_isundersourcecontrol @vchLoginName varchar(255) = '', @vchPassword varchar(255) = '', @iWhoToo int = 0 /* 0 => Just check project; 1 => get list of objs */ as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId = 0 declare @VSSGUID varchar(100) select @VSSGUID = 'SQLVersionControl.VCS_SQL' declare @iReturnValue int select @iReturnValue = 0 declare @iStreamObjectId int select @iStreamObjectId = 0 declare @vchTempText varchar(255) declare @iPropertyObjectId int select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchProjectName varchar(255) declare @vchSourceSafeINI varchar(255) declare @vchServerName varchar(255) declare @vchDatabaseName varchar(255) exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSProject', @vchProjectName OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT if (@vchProjectName IS NULL) or (@vchSourceSafeINI IS NULL) or (@vchServerName IS NULL) or (@vchDatabaseName IS NULL) begin RAISERROR('Not Under Source Control',16,-1) return end if @iWhoToo = 1 begin /* Get List of Procs in the project */ exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAMethod @iObjectId, 'GetListOfObjects', NULL, @vchProjectName, @vchSourceSafeINI, @vchServerName, @vchDatabaseName, @vchLoginName, @vchPassword if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT if @iReturn <> 0 GOTO E_OAError create table #ObjectList (id int identity, vchObjectlist varchar(255)) select @vchTempText = 'STUB' while @vchTempText IS NOT NULL begin exec @iReturn = sp_OAMethod @iStreamObjectId, 'GetStream', @iReturnValue OUT, @vchTempText OUT if @iReturn <> 0 GOTO E_OAError if (@vchTempText IS NOT NULL) insert into #ObjectList (vchObjectlist ) select @vchTempText end select vchObjectlist from #ObjectList order by id end CleanUp: return E_OAError: exec dbo.dt_displayoaerror @iObjectId, @iReturn goto CleanUp dt_isundersourcecontrol dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_isundersourcecontrol dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
31 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:14.577 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:14.577 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create procedure dbo.dt_removefromsourcecontrol as set nocount on declare @iPropertyObjectId int select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') exec dbo.dt_droppropertiesbyid @iPropertyObjectId, null /* -1 is returned by dt_droppopertiesbyid */ if @@error <> 0 and @@error <> -1 return 1 return 0 dt_removefromsourcecontrol dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_removefromsourcecontrol dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
32 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:14.747 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:14.747 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_validateloginparams @vchLoginName varchar(255), @vchPassword varchar(255) as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId =0 declare @VSSGUID varchar(100) select @VSSGUID = 'SQLVersionControl.VCS_SQL' declare @iPropertyObjectId int select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchSourceSafeINI varchar(255) exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAMethod @iObjectId, 'ValidateLoginParams', NULL, @sSourceSafeINI = @vchSourceSafeINI, @sLoginName = @vchLoginName, @sPassword = @vchPassword if @iReturn <> 0 GOTO E_OAError CleanUp: return E_OAError: exec dbo.dt_displayoaerror @iObjectId, @iReturn GOTO CleanUp dt_validateloginparams dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_validateloginparams dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
33 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:14.903 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:14.903 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_vcsenabled as set nocount on declare @iObjectId int select @iObjectId = 0 declare @VSSGUID varchar(100) select @VSSGUID = 'SQLVersionControl.VCS_SQL' declare @iReturn int exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 raiserror('', 16, -1) /* Can't Load Helper DLLC */ dt_vcsenabled dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_vcsenabled dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
34 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:15.637 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:15.637 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_whocheckedout @chObjectType char(4), @vchObjectName varchar(255), @vchLoginName varchar(255), @vchPassword varchar(255) as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId =0 declare @VSSGUID varchar(100) select @VSSGUID = 'SQLVersionControl.VCS_SQL' declare @iPropertyObjectId int select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID') declare @vchProjectName varchar(255) declare @vchSourceSafeINI varchar(255) declare @vchServerName varchar(255) declare @vchDatabaseName varchar(255) exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSProject', @vchProjectName OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT if @chObjectType = 'PROC' begin exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError declare @vchReturnValue varchar(255) select @vchReturnValue = '' exec @iReturn = sp_OAMethod @iObjectId, 'WhoCheckedOut', @vchReturnValue OUT, @sProjectName = @vchProjectName, @sSourceSafeINI = @vchSourceSafeINI, @sObjectName = @vchObjectName, @sServerName = @vchServerName, @sDatabaseName = @vchDatabaseName, @sLoginName = @vchLoginName, @sPassword = @vchPassword if @iReturn <> 0 GOTO E_OAError select @vchReturnValue end CleanUp: return E_OAError: exec dbo.dt_displayoaerror @iObjectId, @iReturn GOTO CleanUp dt_whocheckedout dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_whocheckedout dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]
35 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] 2004-01-20 13:09:16.077 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] NO NO [empty string] NO 2004-01-20 13:09:16.077 [empty string] -1 [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] SQL silverwareconsultingdb create proc dbo.dt_addtosourcecontrol_u @vchSourceSafeINI nvarchar(255) = '', @vchProjectName nvarchar(255) ='', @vchComment nvarchar(255) ='', @vchLoginName nvarchar(255) ='', @vchPassword nvarchar(255) ='' as set nocount on declare @iReturn int declare @iObjectId int select @iObjectId = 0 declare @iStreamObjectId int select @iStreamObjectId = 0 declare @VSSGUID nvarchar(100) select @VSSGUID = N'SQLVersionControl.VCS_SQL' declare @vchDatabaseName varchar(255) select @vchDatabaseName = db_name() declare @iReturnValue int select @iReturnValue = 0 declare @iPropertyObjectId int declare @vchParentId nvarchar(255) declare @iObjectCount int select @iObjectCount = 0 exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT if @iReturn <> 0 GOTO E_OAError /* Create Project in SS */ exec @iReturn = sp_OAMethod @iObjectId, 'AddProjectToSourceSafe', NULL, @vchSourceSafeINI, @vchProjectName output, @@SERVERNAME, @vchDatabaseName, @vchLoginName, @vchPassword, @vchComment if @iReturn <> 0 GOTO E_OAError exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT if @iReturn <> 0 GOTO E_OAError /* Set Database Properties */ begin tran SetProperties /* add high level object */ exec @iPropertyObjectId = dbo.dt_adduserobject_vcs 'VCSProjectID' select @vchParentId = CONVERT(nvarchar(255),@iPropertyObjectId) exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSProjectID', @vchParentId , NULL exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSProject' , @vchProjectName , NULL exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSSourceSafeINI' , @vchSourceSafeINI , NULL exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSSQLServer', @@SERVERNAME, NULL exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName, NULL if @@error <> 0 GOTO E_General_Error commit tran SetProperties declare cursorProcNames cursor for select convert(nvarchar(255), name) from sysobjects where type = N'P' and name not like N'dt_%' open cursorProcNames while 1 = 1 begin declare @vchProcName nvarchar(255) fetch next from cursorProcNames into @vchProcName if @@fetch_status <> 0 break select colid, text into #ProcLines from syscomments where id = object_id(@vchProcName) order by colid declare @iCurProcLine int declare @iProcLines int select @iCurProcLine = 1 select @iProcLines = (select count(*) from #ProcLines) while @iCurProcLine <= @iProcLines begin declare @pos int select @pos = 1 declare @iCurLineSize int select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine)) while @pos <= @iCurLineSize begin declare @vchProcLinePiece nvarchar(255) select @vchProcLinePiece = convert(nvarchar(255), substring((select text from #ProcLines where colid = @iCurProcLine), @pos, 255 )) exec @iReturn = sp_OAMethod @iStreamObjectId, N'AddStream', @iReturnValue OUT, @vchProcLinePiece if @iReturn <> 0 GOTO E_OAError select @pos = @pos + 255 end select @iCurProcLine = @iCurProcLine + 1 end drop table #ProcLines exec @iReturn = sp_OAMethod @iObjectId, 'CheckIn_Stor dt_addtosourcecontrol_u dbo PROCEDURE YES [empty string] [empty string] [empty string] silverwareconsultingdb dt_addtosourcecontrol_u dbo MODIFIES [empty string] [empty string] [empty string] [empty string] [empty string] [empty string] [empty string]