Column name of function in SQL Server

Question

I want column name of table.

So I try this

select * 
from sys.columns 
where object_id = OBJECT_ID('dbo.fnproduct()')

but this didn't show any data ..that's why I try using dynamics SQL.

begin
    declare @sql nvarchar(max) = 'select * from sys.columns  
                                  where object_id=OBJECT_ID('''+dbo.fnproduct()+''')'

    print @sql

    exec sp_executesql @sql
end

but I get this error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnproduct", or the name is ambiguous.

Help me to solve this or suggest me alternative way and ya..I want to pass function here

Answer 1

Try this query :

SELECT c.name [ColumnName] 
FROM sys.columns C
INNER JOIN  sys.objects O ON C.Object_id = O.Object_Id
WHERE O.NAME = 'FunctionName'

Answer 2

You can Get the List of All Columns of a table from the System View INFORMATION_SCHEMA.COLUMNS

Just Select

SELECT
    *
    FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_NAME = 'YouTableName'

Note :

If you are trying to Get the List of Column Name from a Function (That's what I felt while looking at your Code) It is not possible, because Functions does not have a Column Name unless it is a Table Valued Function. In That case Use this

SELECT *
FROM sys.columns
WHERE object_id=object_id('dbo.YourTVF')