September 13, 2004 at 2:21 pm
Is there a way to easily list the fields in a given table?
September 13, 2004 at 2:31 pm
looking for this? sp_help '<table name>'
or
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table name>'
September 14, 2004 at 7:24 am
My favorite method is:
select name from syscolumns where object_name(id) = '<table name>'
dab
September 14, 2004 at 10:14 am
The cumbersome way of using syscolumns was mine until I saw iLoveSQL's post yesterday.
Object_Name doesn't quite cover it, after all.
Everett Wilson
ewilson10@yahoo.com
September 14, 2004 at 12:26 pm
sp_columns will do it
September 14, 2004 at 12:59 pm
Now sp_help '<table name>' is cumbersome.
September 14, 2004 at 3:17 pm
YOu can try this, if you want more specific info.
I didn't write it.
Check the URL where I found the code also:
if exists
(select * from sysobjects
where id = object_id('dbo.usp_get_table_columns') and sysstat & 0xf = 4)
drop procedure dbo.usp_get_table_columns
GO
CREATE Procedure
dbo.usp_get_table_columns -- dbo.DE_get_table_columns 'Slip_Sheet'
( @objname
varchar(128) )
AS
SET NOCOUNT ON
/*
This procedure will get the
Columns, DataType and Size
for the incoming @objname, which when originally coded, was for tables
in the database. It looks like it will work for VIEWS also, but
it was not tested for VIEWS very much.
The code is altered code for sp_help.
The source code was originally found at: http://network.programming-in.net/articles/SQL.asp?SQL=sp_help
*/
declare
@dbname sysname
-- OBTAIN DISPLAY STRINGS FROM spt_values UP FRONT --
declare @no varchar(35), @yes varchar(35), @none varchar(35)
select @no = name from master.dbo.spt_values where type = 'B' and number = 0
select @yes = name from master.dbo.spt_values where type = 'B' and number = 1
select @none = name from master.dbo.spt_values where type = 'B' and number = 2
-- If no @objname given, give a little info about all objects.
if @objname is null
begin
-- DISPLAY ALL SYSOBJECTS --
select
'Name' = o.name,
'Owner' =
user_name(uid),
'Object_type' =
substring(v.name,5,31)
from sysobjects o, master.dbo.spt_values v
where o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
order by Object_type desc, Name asc
--print ' '
-- DISPLAY ALL USER TYPES
select
'User_type' =
name,
'Storage_type' = type_name(xtype),
'Length' = length,
'Prec' =
TypeProperty(name, 'precision'),
'Scale' =
TypeProperty(name, 'scale'),
'Nullable' =
case when TypeProperty(name, 'AllowsNull') = 1
then @yes else @no end,
'Default_name' =
isnull(object_name(tdefault), @none),
'Rule_name' =
isnull(object_name(domain), @none),
'Collation' = collation
from systypes
where xusertype > 256
order by name
--return(0)
end
-- Make sure the @objname is local to the current database.
select @dbname = parsename(@objname,3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
--return(1)
end
-- @objname must be either sysobjects or systypes: first look in sysobjects
declare @objid int
declare @sysobj_type char(2)
select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@objname)
-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
if @objid is null
begin
-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
select @objid = xusertype from systypes where name = @objname
-- IF NOT IN SYSTYPES, GIVE UP
if @objid is null
begin
select @dbname=db_name()
raiserror(15009,-1,-1,@objname,@dbname)
--return(1)
end
-- DATA TYPE HELP (prec/scale only valid for numerics)
select
'Type_name' =
name,
'Storage_type' = type_name(xtype),
'Length' = length,
'Prec' =
TypeProperty(name, 'precision'),
'Scale' =
TypeProperty(name, 'scale'),
'Nullable' =
case when allownulls=1 then @yes else @no end,
'Default_name' =
isnull(object_name(tdefault), @none),
'Rule_name' =
isnull(object_name(domain), @none),
'Collation' = collation
from systypes
where xusertype = @objid
--return(0)
end
-- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO
/*
select
'Name' = o.name,
'Owner' = user_name(uid),
'Type' = substring(v.name,5,31),
'Created_datetime' = o.crdate
from sysobjects o, master.dbo.spt_values v
where o.id = @objid and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
*/
--print ' '
-- DISPLAY COLUMN IF TABLE / VIEW
if @sysobj_type in ('S ','U ','V ','TF','IF')
begin
-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
declare @numtypes nvarchar(80)
select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'
-- INFO FOR EACH COLUMN
--print ' '
select
'Table_name' =
LEFT(@objname, LEN(@objname)) ,
'Column_name' =
name,
'Type' = type_name(xusertype),
--'Computed' = case when iscomputed = 0 then @no else @yes end,
'Length' =
convert(int, length)--,
/*
'Prec' = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),ColumnProperty(id, name, 'precision'))
else ' ' end,
'Scale' = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),OdbcScale(xtype,xscale))
else ' ' end,
'Nullable' = case when isnullable = 0 then @no else @yes end,
'TrimTrailingBlanks' = case ColumnProperty(@objid, name, 'UsesAnsiTrim')
when 1 then @no
when 0 then @yes
else '(n/a)' end,
'FixedLenNullInSource' = case
when type_name(xtype) not in ('varbinary','varchar','binary','char')
Then '(n/a)'
When status & 0x20 = 0 Then @no
Else @yes END,
'Collation' = collation
*/
from syscolumns
where id = @objid and number = 0
order by name --colid
end
SET NOCOUNT OFF
GO
September 14, 2004 at 3:54 pm
Now THAT's cumbersome.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply