Apr
20
Written by:
Steve Gray
4/20/2010 10:30 AM
This simple stored proc will return all the fields in a SQL table, along with the data type. I use this when writing code against larger data structures where I don’t have the file names or data types memorized, it speeds writing procs.
In order to use this, you’ll also need f_4P_columnType.
Code Snippet
- -- 4/19/2010 created
-
- IF EXISTS (SELECT name
- FROM sysobjects
- WHERE name = N'sp_fields'
- AND type = 'P')
- DROP PROCEDURE sp_fields
- GO
-
- CREATE PROCEDURE sp_fields
- -- sp_fields 'sop10100'
-
- @vchrTableName varchar(50)
-
- AS
-
- select syscolumns.name , dbo.f_4P_columnType(xusertype,length,xprec,xscale) as fieldType
- from sysobjects
- left join syscolumns on syscolumns.id = sysobjects.id
- where sysobjects.name = @vchrTableName
- order by 1
-
- go
As always, I welcome your comments!