Use Admin
IF Object_ID('dbo.udfGetColumnList')
Is Not Null
DROP
FUNCTION dbo.udfGetColumnList
go
CREATE FUNCTION dbo.udfGetColumnList(
@SchemaName sysname,
@ObjName sysname
)
RETURNS TABLE
AS
/* DATE AUTHOR REMARKS
3/1/14 PPaiva Initial creation.
DESCRIPTION
Returns
a list of columns with attributes for
a
given view or table in the current database.
USAGE
SELECT *
FROM udfGetColumnList('sys', 'syssqlguides')
SELECT *
FROM udfGetColumnList('dbo', 'DelimitedSplit8K')
SELECT *
FROM udfGetColumnList('dbo', 'udfGetColumnList')
*/
RETURN
SELECT o.Type ObjTypeAbbrev,
CASE WHEN o.Type = 'U' THEN 'Table'
WHEN o.Type = 'V' THEN 'View'
WHEN o.Type = 'TF' THEN 'FunctionTableTVF'
WHEN o.Type = 'IF' THEN 'FunctionInline'
ELSE 'Undefined' END ObjType,
c.column_id ColID,
s.name COLLATE
SQL_Latin1_General_CP1_CI_AS SchemaName,
o.Name COLLATE
SQL_Latin1_General_CP1_CI_AS ObjName,
c.Name COLLATE
SQL_Latin1_General_CP1_CI_AS ColName,
t.Name COLLATE
SQL_Latin1_General_CP1_CI_AS Datatype,
c.max_length Length,
CASE WHEN t.Name COLLATE
SQL_Latin1_General_CP1_CI_AS In ('char', 'varchar', 'nchar', 'nvarchar')
THEN t.Name COLLATE SQL_Latin1_General_CP1_CI_AS + '(' +
CASE WHEN c.max_Length = -1 THEN 'max' ELSE Convert(varchar, c.max_length) END
+ ')'
ELSE t.Name COLLATE
SQL_Latin1_General_CP1_CI_AS
END CodeDatatype,
c.Is_Computed IsCalc,
c.Is_Identity IsIdentity,
c.is_nullable IsNullable,
o.object_id,
o.create_date ObjCreateDate,
o.modify_date ObjModifyDate
FROM sys.objects o
LEFT JOIN sys.columns c
ON
c.object_id = o.object_id
JOIN sys.schemas s
ON
s.schema_id = o.schema_id
LEFT JOIN sys.types t
ON
t.user_type_id =
c.system_type_id
WHERE o.name = @ObjName
AND
s.name =
@SchemaName