SQL Server Performance

Script Function using Stored procedure

Discussion in 'SQL Server 2005 General DBA Questions' started by shabnyc, Aug 23, 2007.

  1. shabnyc Member

    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
  2. ndinakar Member

    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.
  3. shabnyc Member

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

    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
  5. shabnyc Member

    thanks simondm. I am looking but found nothing. still looking though
  6. satya Moderator

  7. shabnyc Member

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

  9. shabnyc Member

    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,
  10. satya Moderator

    IMHO using SMO in thsi case doesn't hurt performance at all, you could probably run that permission script alone to see whether it is extracting results or not.
  11. shabnyc Member

    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.
  12. zeersalim New Member

    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
  13. satya Moderator

    Are you using against SQL 2000 or 2005 instance?
  14. shabnyc Member

    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
  15. satya Moderator

    I don't understand the 'dump the code' aspect as you were told, also having such blank line might be caused due to -w swith in OSQL. As you are using SQL 2005 try with SQLCMD in thsi case.
  16. shabnyc Member

    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
  17. satya Moderator

    Can you take help fromDMO or SMO?
  18. shabnyc Member

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

    I don't say it is a best way forward, but better than usual availble methods from Enterprise Manager or Management Studio in SQL SErver.

Share This Page