Script Function using Stored procedure

Hi,
I need to write an SP that script each user defined function with its permessions, and put this code (with the permissions) in an SQL file. the name of the file should be the name of the function with .SQL extention. anyone has any idea on how to create this kind of procedure?
thanks in advance
Shabnyc

You dont need to write an sp for that. right click on your db -> all Tasks -> Generate scripts. select the functions and get the .sql file.
If you really want to write code to automate this, fire up profiler and see what SQL Server is doing to generate the script and mimic it.

Hi ndinakar,
do you think this could be done using SQLcmd utility?.. generating script and saving them to .SQL file

I think the only way to script objects in an automated fashion is to write something using SMO (DMO in 2000 & pre 2000).
Though if you do a search with you favorite search engine I'm sure you'll find some programs already written that can be called from command line.
Simon

thanks simondm. I am looking but found nothing. still looking though

thanks Satya, I just got feedback from management, said they need a stored procedure to do this, not a DMO nor SMO.

thanks Satya.
the reason is that they dont want to have more burden on the server(performance thing). I will make this easy. I've created the following script:declare
@functionName varchar(256) declare @sql varchar(1024) declare
my_cur cursor for select
name from sysobjects where type in ('TF', 'FN', 'IF', 'FS', 'FT')open
my_cur fetch
next from my_cur into @functionName while
@@fetch_status = 0 begin set @sql = 'Osql -Sgessralaptop -E -n
-ddatabaseName -Q "set nocount on create #ScriptTable table(helptext
varchar(512)) insert into #ScriptTable exec sp_helptext '
+@functionName+' select rtrim(helptext) from #ScriptTable" -o C:TTIFunctions'+@functionName+'.sql'
--print @sql exec master..xp_cmdshell @sql
fetch next from my_cur into @functionName endclose
my_cur deallocate
my_cur
-- this script do not run correctly, the file functionName.sql that supposed to create after running does not get created. also, couldnt find a way to add users permission for every function get into the cursor.
thanks in advance,

Satya, the script you've provided runs perfect. thanks a lot.
the result of the script should be added to the functionName.sql file generated by my code.

Hi all. this is an interesting topic. I would love to have this kind of script. however I tried to run the script below and it did not seem to work fine, it returns null for all objects. I used it to script SPs instead of function. anyone knows why it is not operating properly?
thanks
new member
zeer salim
---
---
declare @functionName varchar(256)
declare @sql varchar(1024) declare
my_cur cursor for select
name from sysobjects where type in ('TF', 'FN', 'IF', 'FS', 'FT')open
my_cur fetch
next from my_cur into @functionName while @@fetch_status = 0 begin set @sql = 'Osql -Sgessralaptop -E -n -ddatabaseName -Q "set nocount on create #ScriptTable table(helptext
varchar(512)) insert into #ScriptTable exec sp_helptext '
+@functionName+' select rtrim(helptext) from #ScriptTable" -o C:TTIFunctions'+@functionName+'.sql'
--print @sql exec master..xp_cmdshell @sql
fetch next from my_cur into @functionName endclose
my_cur deallocate
my_cur

Hi satya, thanks for replying. it is SQL server 2005
my code now scripts all functions, creates a .sql file for each one and copy text of functions into there .sql files.so it is about 99% correct. the only problem is when I open the created functionName.sql file I see a break empty line after each line code of the original function. I want to get rid of those empty lines so it looks exact with the source code. some one says dump the code, I dont really know how to dump the original code to another file. the following is what I've got:

DECLARE @functionName VARCHAR(256)
DECLARE @sql VARCHAR(1024)
DECLARE @DBName VARCHAR(50)

SET @DBName = (SELECT db_name())
DECLARE my_cur CURSOR for
SELECT [Name] FROM sysobjects WITH (NOLOCK) WHERE [Type] in ('TF', 'FN', 'IF', 'FS', 'FT')
PRINT 'Creating Generate Script files for database Functions'
OPEN my_cur
FETCH next FROM my_cur INTO @functionName
WHILE @@FETCH_status = 0
BEGIN
SET @sql = 'osql -S ServerName-E -n -w5000 -h-1 -d '+@DBName+' -Q "SET nocount on create table #ScriptTable(helptext VARCHAR(1000)) insert into #ScriptTable exec sp_helptext '+@FunctionName+' Select ltrim(helptext) FROM #ScriptTable" -o C:Functions'+@FunctionName+'.sql'
--print @sql
EXEC master..xp_cmdshell @sql
FETCH next FROM my_cur INTO @functionName
END
CLOSE my_cur
DEALLOCATE my_cur
-- thanks in advance

dumping the code means taking the original script file and coping it to the destination without any changes to the original script. if I dont use -w, the length of each line will be set to default which is 80 character while some script lines exceed 250 characters. so -w does Ok.
yep I used sqlcmd and found switch -k. this switch deletes all empty lines and any tabs and replaces them with a single space. but this made the original script look improffessional work. What I want is just removing the empty lines not the tabs.
if u run the code below in your home SQL instance and change the destination folder and SserverName you will get what exactly what I do need.
thanks

if thats the only way, then I have to use the DMO or SMO becasue I dont have any other choice