sqlzoo - SQL training

https://sqlzoo.net/wiki/SELECT_basics

Columns tables and types

select schema_name(o.schema_id) as SchemaName, object_name(o.object_id) as TableName, c.name as ColumnName, t.name as ColumnType

from sys.columns c

join sys.types t on c.system_type_id = t.system_type_id

join sys.objects o on c.object_id = o.object_id

where c.name = 'SUG-MUTZAR' or c.name = 'STATUS'

order by c.name

Cannot perform alter on a function because it is an incompatible object type

If you have a Multi-Statement Table-Valued Function (MSTVF):

CREATE FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]

( ... )

RETURNS @ret TABLE (ID INT)

AS

BEGIN

...

RETURN

END

If you will try to alter it to – with changing it to an Inline Table-Valued Function:

ALTER FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]

(

       @ClientPolTable [DataAccess].[TT_ClientPoliciesByTZ] READONLY,

       @SugMimshak INT,

       @FromNechonutDate DATETIME = NULL, -- In case no date is selected

       @ToNechonutDate DATETIME = NULL

)

RETURNS TABLE AS

RETURN

(  

       SELECT .....

)

You will be got this error:

Cannot perform alter on 'DataAccess.ufn_GetLastPoliciesByTZ' because it is an incompatible object type.

Solution:

You can't alter a function from MSTVF to Inline. So, drop and create again the function.

CREATE FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]() RETURNS TABLE AS RETURN (SELECT 1 AS ID)