DECLARE object_cursor CURSOR FOR
SELECT DISTINCT id
FROM syscomments sc (NOLOCK)
WHERE sc.id > 0
AND OBJECTPROPERTY(sc.id, 'IsMSShipped') = 0
AND [TEXT] IS NOT NULL
INSERT INTO master..coSourceControl
OUTPUT ''1'' INTO #Results
SELECT s0.*
FROM (select db_id() as databaseid,
@object_id as objectid,
@object_name as objectname,
@ObjectText as objecttext,
@SourceTimestamp as sourcedate) s0
left
join (SELECT i1.*
from master..coSourceControl i1
join (SELECT databaseid, objectid, MAX(SourceDate) as SourceDate
FROM master..coSourceControl
GROUP BY databaseid, objectid) i2
on i1.databaseid = i2.databaseid
and i1.objectid = i2.objectid
and i1.sourcedate = i2.sourcedate) s1
on s0.databaseid = s1.databaseid
and s0.objectid = s1.objectid
WHERE s0.objectname <> isnull(s1.objectname, '''')
OR s0.objecttext <> isnull(s1.objecttext, '''')
CREATE TABLE #Results (ObjectsUpdated INT)
DECLARE @ObjectsUpdated INT
exec sp_msforeachdb @SQL
SELECT @ObjectsUpdated = ISNULL(SUM(ObjectsUpdated), 0)
FROM #Results
SELECT @ObjectsUpdated as [Objects Updated]
RETURN @ObjectsUpdated
SELECT db_name(databaseid) as [Database],
object_name(objectid) as [Object Name],
SourceDate,
ObjectText
FROM master.dbo.coSourceControl
WHERE db_name(databaseid) LIKE '%Your database name%'
object_name(objectid) LIKE '%The name of some object%'
ORDER BY SourceDate DESC
ALTER PROC dbo.coSourceControlRefresh
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM master..sysobjects where name like 'coSourceControl')
CREATE TABLE master..coSourceControl (databaseid INT, objectid INT, ObjectName [sysname] NOT NULL, ObjectText NVARCHAR(MAX), SourceDate DATETIME)
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
USE ?
SET NOCOUNT ON
DECLARE @SourceTimestamp DATETIME,
@Object_id INT,
@Object_name SYSNAME,
@ObjectText NVARCHAR(MAX)
SET @SourceTimestamp = GETDATE()
DECLARE object_cursor CURSOR FOR
SELECT DISTINCT id
FROM syscomments sc (NOLOCK)
WHERE sc.id > 0
AND OBJECTPROPERTY(sc.id, ''IsMSShipped'') = 0
AND [TEXT] IS NOT NULL
OPEN object_cursor;
FETCH NEXT FROM object_cursor
INTO @object_id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ObjectText = ''''
SELECT @ObjectText = @ObjectText + [text]
FROM syscomments
where id = @object_id
order by colid
SELECT @Object_Name = object_name(@object_id)
INSERT INTO master..coSourceControl
OUTPUT ''1'' INTO #Results
SELECT s0.*
FROM (select db_id() as databaseid,
@object_id as objectid,
@object_name as objectname,
@ObjectText as objecttext,
@SourceTimestamp as sourcedate) s0
left
join (SELECT i1.*
from master..coSourceControl i1
join (SELECT databaseid, objectid, MAX(SourceDate) as SourceDate
FROM master..coSourceControl
GROUP BY databaseid, objectid) i2
on i1.databaseid = i2.databaseid
and i1.objectid = i2.objectid
and i1.sourcedate = i2.sourcedate) s1
on s0.databaseid = s1.databaseid
and s0.objectid = s1.objectid
WHERE s0.objectname <> isnull(s1.objectname, '''')
OR s0.objecttext <> isnull(s1.objecttext, '''')
FETCH NEXT FROM object_cursor
INTO @object_id
END
CLOSE object_cursor;
DEALLOCATE object_cursor;'
-- Actually run it for each database
CREATE TABLE #Results (ObjectsUpdated INT)
DECLARE @ObjectsUpdated INT
exec sp_msforeachdb @SQL
SELECT @ObjectsUpdated = ISNULL(SUM(ObjectsUpdated), 0)
FROM #Results
SELECT @ObjectsUpdated as [Objects Updated]
RETURN @ObjectsUpdated
END
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)