Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

changes objects ? Expand / Collapse
Author
Message
Posted Friday, December 28, 2007 8:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 3, 2012 5:22 AM
Points: 10, Visits: 56
Can someone help me with this :

I'd like to know how i can find every stored procedure/trigger .. table which has been changes since a certain date.

How can I do this ?

For the moment I'm browsing through the tree view in the Management Studio but i guess there must be a better way

Thanks
Post #437066
Posted Friday, December 28, 2007 8:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
select * from sys.procedures where modify_date > @Date
select * from sys.triggers where modify_date > @date

Or, if you want to check all objects

select * from sys.objects where modify_date > @date



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #437074
Posted Friday, December 28, 2007 12:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 3, 2012 5:22 AM
Points: 10, Visits: 56
Thank You, This was something i was looking for.

Is there also a way to export the found objects in a script to a text file so I can import them
on another server ?

Post #437181
Posted Friday, December 28, 2007 1:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
Easiest way is to use a cursor. For each item found, call sp_helptext.
Set the output in management studio to text, then you can save the whole lot as a file

SET NOCOUNT ON
DECLARE @ObjectName SYSNAME
DECLARE curRecentItems CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.procedures WHERE modify_date > @SomeDate

OPEN curRecentItems

FETCH NEXT FROM curRecentItems INTO @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_helpText @ObjectName
FETCH NEXT FROM curRecentItems INTO @ObjectName
END

CLOSE curRecentItems
DEALLOCATE curRecentItems



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #437209
Posted Friday, December 28, 2007 2:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 2,278, Visits: 3,054
Great solution Gila :)

Bart,
Do not forget to remove column headers in the results to text; otherwise, you will have the word "text" before each stored procedure.




My blog: http://jahaines.blogspot.com
Post #437235
Posted Wednesday, January 9, 2008 6:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 28, 2013 7:49 AM
Points: 14, Visits: 46
Hi,

how do you remove the default column (text) and underlines (----------------)?

Thanks
kj
Post #440569
Posted Wednesday, January 9, 2008 6:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
Easiest thing is just search and replace before you save the script


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #440570
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse