MSSQL function get a list of fields of a particular table from system table

Below is the actual code so you can just copy and paste to create the store procedure.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ftvDescribe]
(
@tablename varchar(256)

)
RETURNS
TABLE
AS
RETURN
(
-- Fill the table variable with the rows for your result set
SELECT DISTINCT sCols.colid AS 'ID', sCols.name as FieldName, sTyps.name as FieldType, sCols.length as FieldLen
FROM [syscolumns] sCols
INNER JOIN [systypes] sTyps ON sCols.xtype = sTyps.xtype
INNER JOIN [sysobjects] sObjs ON sObjs.id = sCols.[id]
AND UPPER(sObjs.name) = UPPER(@tablename)
where sTyps.name <> 'sysname'

)

Forums: