changes objects ?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » changes objects ?

changes objects ?

Rate Topic

Display Mode

Topic Options

Author

Message

Bart Noels

Posted Friday, December 28, 2007 8:05 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, June 03, 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

GilaMonster

Posted Friday, December 28, 2007 8:11 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 37,726, Visits: 29,988

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

Bart Noels

Posted Friday, December 28, 2007 12:27 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, June 03, 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

GilaMonster

Posted Friday, December 28, 2007 1:29 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 37,726, Visits: 29,988

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

Adam Haines

Posted Friday, December 28, 2007 2:15 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:39 PM
Points: 2,278, Visits: 2,998

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

vijayakumarkj-602216

Posted Wednesday, January 09, 2008 6:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:59 AM
Points: 7, Visits: 35

Hi,

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

Thanks
kj

Post #440569

GilaMonster

Posted Wednesday, January 09, 2008 6:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 37,726, Visits: 29,988

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 »

Permissions