In sql server 2005/2008, can anyone please advise me on how can i generate scripts automatically of all the objects that let say starts with "Client_"
I already have a query that identifies all the object that starts from "client_", but i also want to generate there scripts automatically. Doing it manually is a huge task.
select name,type_desc
from sys.objects
where object_definition(object_id) like '%client_%'
Please advise
Massive thanks Amit
I think you should use sp_helptext system procedure within your select statement.
Also you could use database cursor to perform execution of sp_helptext in a loop:
DECLARE @name SYSNAME -- object name
DECLARE @type_desc VARCHAR(MAX) -- path for backup files
DECLARE @sql VARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
select name,type_desc
from sys.objects
where object_definition(object_id) like '%client_%'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name, @type_desc
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql =
'sp_helptext @objname = ''' + @name + ''''
EXEC (@sql)
FETCH NEXT FROM db_cursor INTO @name, @type_desc
END
CLOSE db_cursor
DEALLOCATE db_cursor
Additional solution is to use sys.syscomments system view. Example:
SELECT o.name, o.[type], s.[text]
from sys.objects o
INNER JOIN sys.syscomments s ON o.[object_id] = s.id
where object_definition(object_id) like '%client_%'
I think there is not tool that will solve your purpose. else use generate script for all and remove extra script that you don't need.
