SQL Server/T-SQL Tutorial/System Settings/OBJECTPROPERTY

Check with OBJECTPROPERTY function

5>    CREATE FUNCTION dbo.AveragePrice()
6>    RETURNS money
7>    WITH SCHEMABINDING
8>    AS
9>    BEGIN
10>       RETURN (SELECT AVG(Price) FROM dbo.Titles)
11>    END
12> GO
1>
2>    SELECT OBJECTPROPERTY(OBJECT_ID("AveragePrice"), "IsDeterministic")
3> GO
-----------
          1
(1 rows affected)
1>
2> drop FUNCTION dbo.AveragePrice;
3> GO

Returning Information about User-Defined Functions

Syntax for the OBJECTPROPERTY() Function
OBJECTPROPERTY ( <object_id> , <property> )
IsScalarFunction
IsInlineFunction
IsTableFunction
IsDeterministic
IsSchemaBound
SELECT
  OBJECTPROPERTY(OBJECT_ID("dbo.fibonacci"), "IsScalarFunction")

Use OBJECTPROPERTY and OBJECT_ID to check the table existance

5> USE master
6> SELECT OBJECTPROPERTY(OBJECT_ID("syslockinfo"), "TableIsFake"),
7>     OBJECTPROPERTY(OBJECT_ID("sysobjects"), "TableIsFake")
8> GO
Changed database context to "master".
----------- -----------
       NULL        NULL
(1 rows affected)
1>

Using the OBJECTPROPERTY Function to Check Whether a View is Indexable

SELECT OBJECTPROPERTY(OBJECT_ID("view_name"), "IsIndexable")