Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScheck_functions_directly_in_view

  No additional text.


Syntax

-- Requires Certificate signature for catalog access
create procedure sys.sp_MScheck_functions_directly_in_view @viewname nvarchar(400)
                                                            , @has_dynamic_filters bit output
                                                            , @dynamic_filters_function_list nvarchar(500) output
                                                            , @uses_host_name bit output
                                                            , @uses_suser_sname bit output
as
    declare @built_in_function sysname
    , @built_in_function_without_parens sysname
    , @id int
    , @function_name nvarchar(270)
    , @retcode int

    create table #view_text_table (viewtext nvarchar(max)) -- will be nvarchar(max) ultimately.
    declare @functions_used_table table(functionname sysname, object_id int default 0, id int identity)
    declare @view_id int, @iteration int

    select @view_id = object_id(@viewname)

    insert into @functions_used_table (functionname, object_id)
    select schema_name(schema_id) + '.' + name + '()', object_id
    from sys.objects where object_id in
    (
        select referenced_major_id
        from sys.sql_dependencies where object_id = @view_id
        and referenced_major_id in (select object_id from sys.objects where type = 'FN')
    )

    -- delete from @functions_used_table any functions that are not really dynamic functions.
    -- a function is considered dynamic if
    -- (1) it has host_name() or suser_sname() or other server built-in functions in its text, or
    -- (2) it references other functions which are dynamic, or
    -- (3) it references views. we should really expand the views and see if they have a dynamic function
    -- hidden somewhere - but this will make the checking code to be too complex. It is easier to assume that
    -- a UDF referencing a view is dynamic. This is an extremely rare case anyway.

    select top 1 @function_name = functionname
                    , @id = id
    from @functions_used_table
    order by id

    while @function_name is not null
    begin
        select @has_dynamic_filters = 0

        select @function_name = substring(@function_name, 1, len(@function_name)-2)

        exec @retcode = sys.sp_MSisdynamicfunction @function_name, @has_dynamic_filters output,
                                                    @uses_host_name output, @uses_suser_sname output
        if @@error <> 0 or @retcode <> 0
            return 1

        if @has_dynamic_filters = 0
            delete from @functions_used_table where id = @id

        set @function_name = NULL

        select top 1 @function_name = functionname
                    , @id = id
        from @functions_used_table
        where id > @id
        order by id
    end

    if exists (select * from @functions_used_table a where exists
                        (select b.object_id from sys.parameters b
                            where a.object_id = b.object_id
                            and b.is_output = 0))
    begin
        select top 1 @function_name = substring(functionname, 1, len(functionname)-2)
            from @functions_used_table a where exists
                        (select b.object_id from sys.parameters b
                            where a.object_id = b.object_id
                            and b.is_output = 0)
        raiserror(22560, 16, -1, @function_name)
        return 1
    end

    insert into #view_text_table exec sp_MSrepl_helptext @viewname

    set @iteration = 0

    while (1=1)
    begin
        if @iteration = 0
            select @built_in_function = 'host_name()', @built_in_function_without_parens = 'host_name'
        else if @iteration = 1
            select @built_in_function = 'suser_sname()', @built_in_function_without_parens = 'suser_sname'
        else if @iteration = 2
            select @built_in_function = 'suser_name()', @built_in_function_without_parens = 'suser_name'
        else if @iteration = 3
            select @built_in_function = 'user_name()', @built_in_function_without_parens = 'user_name'
        else if @iteration = 4
            select @built_in_function = 'user_id()', @built_in_function_without_parens = 'user_id'
        else if @iteration = 5
            select @built_in_function = 'suser_id()', @built_in_function_without_parens = 'suser_id'
        else if @iteration = 6
            select @built_in_function = 'user_sid()', @built_in_function_without_parens = 'user_sid'
        else if @iteration = 7
            select @built_in_function = 'suser_sid()', @built_in_function_without_parens = 'suser_sid'
        else
            break

        if exists (select * from #view_text_table
                    where REPLACE(REPLACE(REPLACE(REPLACE(UPPER(viewtext), char(0x20),''), char(0x09),''), char(0x0D),''), char(0x0A),'')
                    like ('%' + UPPER(@built_in_function) + '%')
                    and sys.fn_MSisfilteredcolumn(UPPER(viewtext), UPPER(@built_in_function_without_parens), NULL) = 1)
        begin
            if @iteration = 0
                set @uses_host_name = 1
            if @iteration = 1
                set @uses_suser_sname = 1

            select @built_in_function = UPPER(@built_in_function)

            insert into @functions_used_table (functionname) values (@built_in_function)
        end

        select @iteration = @iteration + 1
    end

    if exists (select * from @functions_used_table)
    begin
        select @has_dynamic_filters = 1

        select top 1 @function_name = functionname
                    , @id = id
        from @functions_used_table
        order by id

        while @function_name is not null
        begin
            if @dynamic_filters_function_list is null
                select @dynamic_filters_function_list = @function_name
            else
            begin
                select @dynamic_filters_function_list = @dynamic_filters_function_list + ';' + @function_name

                exec @retcode = sys.sp_MSget_distinct_semicolon_sep_items @dynamic_filters_function_list = @dynamic_filters_function_list output
				if @@error <> 0 or @retcode <> 0
					return 1
            end

            set @function_name = NULL

            select top 1 @function_name = functionname
                        , @id = id
            from @functions_used_table
            where id > @id
            order by id
        end
    end

    return 0

 
Last revision 2008RTM
See also

  sp_check_join_filter (Procedure)
sp_check_subset_filter (Procedure)
sp_MScheck_functions_indirectly_in_view (Procedure)
sp_MScheck_join_filters (Procedure)
sp_MScheck_subset_filters (Procedure)
sp_MSdrop_rladmin (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash