Cannot perform alter on 'dbo.ObjectName' because it is an incompatible object type

  Today, when I tried to Alter a function in my database, I got the error info in the title. Then I tried some example function operations, and found the reason: Inline table-value function and Multi-Statement table-valued function are actually different object type, and you can never do Alter one object to other types(If I am wrong here, please tell me).
Now you could try it yourself:

-- First, we create a inline-table function which returns a table
Create   Function  dbo.yukunFunctionTest ( @param   int ,  @param1   int )
returns   table
AS
RETURN
(
     select   1   as  id
)

-- Then, we want to alter it to a multi-statement table-valued function,
-- and we will get an error here
alter   Function  dbo.yukunFunctionTest ( @param   int ,  @param1   int )
returns   @tt   table
(
    id  int
) 
begin
     insert   into   @tt   values  ( 1 )
     return
end

  By the way, you can get the type value and type desc of any objects in your database from table sys.objects.
  And, which is very important, if  you really want to "alter" the type of your function, actually you need to drop it first, then create a new one. When you do it, pay attention to the permissions to the dropped objects, because you will lose all the permission info when you drop it, and you need to restore them after you create the new one.Bellow is a demo scripts which could do this for you:

select   @grantee   varchar ( 200 )

select   @grantee = name
from  syspermissions p
join  sysusers u  on  p.grantee  =  u.uid
where  id  =   object_id ( ' ObjectName ' )

grant   select   on  dbo.ObjectName  to   @grantee

 

 

转载于:https://www.cnblogs.com/xingyukun/archive/2008/05/21/1203711.html