SELECT table field names

[Randy]

I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated.
Randy

[Ray Miao]

Select from information schema view columns.


------------
Randy at 11/14/01 1:27:17 PM


I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated.
Randy

[Randy]

Thanks Ray. I tried that but I must be getting the syntax wrong. Can you post a sample for a table called MATRIX?
Randy

------------
Ray Miao at 11/14/2001 1:32:32 PM

Select from information schema view columns.


------------
Randy at 11/14/01 1:27:17 PM


I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated.
Randy

I believe he meant:

select * from information_schema.columns


------------
Randy at 11/14/2001 5:00:01 PM


Thanks Ray. I tried that but I must be getting the syntax wrong. Can you post a sample for a table called MATRIX?
Randy

------------
Ray Miao at 11/14/2001 1:32:32 PM

Select from information schema view columns.


------------
Randy at 11/14/01 1:27:17 PM


I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated.
Randy

[Jay Haines]

Try:

SELECT
sysobjects.id as TableID,
sysobjects.name as Table_Name,
syscolumns.name as Field_Name,
systypes.name as Field_Type,
systypes.length as Field_Length,
syscolumns.isnullable as Allow_Null,
syscomments.text as Default_Value
FROM syscolumns
INNER JOIN systypes on systypes.xusertype = syscolumns.xusertype
INNER JOIN sysobjects on sysobjects.id = syscolumns.id
LEFT OUTER JOIN sysobjects as Defaults on Defaults.id = syscolumns.cdefault
LEFT OUTER JOIN syscomments on syscomments.id = Defaults.id
WHERE sysobjects.xtype = 'U'
order by sysobjects.name, syscolumns.colorder

This'll give more info than you're looking for, but you can pare it down by reducing the joins and/or adding to the WHERE clause for the table name.

Jay


------------
at 11/15/2001 2:10:10 PM

I believe he meant:

select * from information_schema.columns


------------
Randy at 11/14/2001 5:00:01 PM


Thanks Ray. I tried that but I must be getting the syntax wrong. Can you post a sample for a table called MATRIX?
Randy

------------
Ray Miao at 11/14/2001 1:32:32 PM

Select from information schema view columns.


------------
Randy at 11/14/01 1:27:17 PM


I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated.
Randy

[Randy]

Thanks Jay, I'll give it a try.
Randy


------------
Jay Haines at 11/15/2001 5:50:26 PM

Try:

SELECT
sysobjects.id as TableID,
sysobjects.name as Table_Name,
syscolumns.name as Field_Name,
systypes.name as Field_Type,
systypes.length as Field_Length,
syscolumns.isnullable as Allow_Null,
syscomments.text as Default_Value
FROM syscolumns
INNER JOIN systypes on systypes.xusertype = syscolumns.xusertype
INNER JOIN sysobjects on sysobjects.id = syscolumns.id
LEFT OUTER JOIN sysobjects as Defaults on Defaults.id = syscolumns.cdefault
LEFT OUTER JOIN syscomments on syscomments.id = Defaults.id
WHERE sysobjects.xtype = 'U'
order by sysobjects.name, syscolumns.colorder

This'll give more info than you're looking for, but you can pare it down by reducing the joins and/or adding to the WHERE clause for the table name.

Jay


------------
at 11/15/2001 2:10:10 PM

I believe he meant:

select * from information_schema.columns


------------
Randy at 11/14/2001 5:00:01 PM


Thanks Ray. I tried that but I must be getting the syntax wrong. Can you post a sample for a table called MATRIX?
Randy

------------
Ray Miao at 11/14/2001 1:32:32 PM

Select from information schema view columns.


------------
Randy at 11/14/01 1:27:17 PM


I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated.
Randy

[Clar]

Hi Randy,
Here is, some intresting Stored Procedure that can help you more.
Sp_Help <Tablename> displays all the information regarding a given table. When information is required only on one particular field then this stored procedure can be used with SQL Server 7.0. Syntax: sp_HelpTableField &#39;TableName&#39; , &#39;Substring of FieldName&#39;. All the field names matching this &#39;Substring&#39; will be displayed.



create proc sp_helpTableField
@objname nvarchar(776) = NULL, -- object name we&#39;re after
@Field nvarchar(50) = NULL -- field name we&#39;re after
as
-- PRELIMINARY
set nocount on
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 = &#39;B&#39; and number = 0
select @yes = name from master.dbo.spt_values where type = &#39;B&#39; and number = 1
select @none = name from master.dbo.spt_values where type = &#39;B&#39; and number = 2

-- If no @objname given, give a little info about all objects.
if @objname is null
begin
-- DISPLAY ALL SYSOBJECTS --
select
&#39;Name&#39; = o.name,
&#39;Owner&#39; = user_name(uid),
&#39;Object_type&#39; = substring(v.name,5,31)
from sysobjects o, master.dbo.spt_values v
where o.xtype = substring(v.name,1,2) and v.type = &#39;O9T&#39;
order by Object_type desc, Name asc

print &#39; &#39;

-- DISPLAY ALL USER TYPES
select
&#39;User_type&#39; = name,
&#39;Storage_type&#39; = type_name(xtype),
&#39;Length&#39; = length,
&#39;Prec&#39; = TypeProperty(name, &#39;precision&#39,
&#39;Scale&#39; = TypeProperty(name, &#39;scale&#39,
&#39;Nullable&#39; = case when TypeProperty(name, &#39;AllowsNull&#39 = 1
then @yes else @no end,
&#39;Default_name&#39; = isnull(object_name(tdefault), @none),
&#39;Rule_name&#39; = isnull(object_name(domain), @none)
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
&#39;Type_name&#39; = name,
&#39;Storage_type&#39; = type_name(xtype),
&#39;Length&#39; = length,
&#39;Prec&#39; = TypeProperty(name, &#39;precision&#39,
&#39;Scale&#39; = TypeProperty(name, &#39;scale&#39,
&#39;Nullable&#39; = case when allownulls=1 then @yes else @no end,
&#39;Default_name&#39; = isnull(object_name(tdefault), @none),
&#39;Rule_name&#39; = isnull(object_name(domain), @none)
from systypes
where xusertype = @objid

return(0)
end

-- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO
select
&#39;Name&#39; = o.name,
&#39;Owner&#39; = user_name(uid),
&#39;Type&#39; = substring(v.name,5,31),
&#39;Created_datetime&#39; = o.crdate
from sysobjects o, master.dbo.spt_values v
where o.id = @objid and o.xtype = substring(v.name,1,2) and v.type = &#39;O9T&#39;

print &#39; &#39;

-- DISPLAY COLUMN IF TABLE / VIEW
if @sysobj_type in (&#39;S &#39;,&#39;U &#39;,&#39;V &#39
begin

-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
declare @numtypes nvarchar(80)
select @numtypes = N&#39;tinyint,smallint,decimal,int,real,money,floa t,numeric,smallmoney&#39;

-- INFO FOR EACH COLUMN
print &#39; &#39;
select
&#39;Column_name&#39; = name,
&#39;Type&#39; = type_name(xusertype),
&#39;Computed&#39; = case when iscomputed = 0 then @no else @yes end,
&#39;Length&#39; = convert(int, length),
&#39;Prec&#39; = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),ColumnProperty(id, name, &#39;precision&#39)
else &#39; &#39; end,
&#39;Scale&#39; = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),OdbcScale(xtype,xscale))
else &#39; &#39; end,
&#39;Nullable&#39; = case when isnullable = 0 then @no else @yes end,
&#39;TrimTrailingBlanks&#39; = case ColumnProperty(@objid, name, &#39;UsesAnsiTrim&#39
when 1 then @no
when 0 then @yes
else &#39;(n/a)&#39; end,
&#39;FixedLenNullInSource&#39; = case
when type_name(xtype) not in (&#39;varbinary&#39;,&#39;varchar&#39;,&#39;binary &#39;,&#39;char&#39
Then &#39;(n/a)&#39;
When status & 0x20 = 0 Then @no
Else @yes END
from syscolumns where id = @objid
and name like &#39;%&#39;+@Field+&#39;%&#39; -- Neeraj Hora
order by colid
-- IDENTITY COLUMN?
print &#39; &#39;
declare @colname sysname
select @colname = name from syscolumns where id = @objid
and colstat & 1 = 1
select
&#39;Identity&#39; = isnull(@colname,&#39;No identity column defined.&#39,
&#39;Seed&#39; = ident_seed(@objname),
&#39;Increment&#39; = ident_incr(@objname),
&#39;Not For Replication&#39; = ColumnProperty(@objid, @colname, &#39;IsIDNotForRepl&#39

-- ROWGUIDCOL?
print &#39; &#39;
select @colname = null
select @colname = name from syscolumns where id = @objid
and ColumnProperty(@objid, name, &#39;IsRowGuidCol&#39 = 1
select &#39;RowGuidCol&#39; = isnull(@colname,&#39;No rowguidcol column defined.&#39

end

-- DISPLAY PROC PARAMS
if @sysobj_type in (&#39;P &#39 --RF too?
begin
-- ANY PARAMS FOR THIS PROC?
if exists (select id from syscolumns where id = @objid)
begin
-- INFO ON PROC PARAMS
print &#39; &#39;
select
&#39;Parameter_name&#39; = name,
&#39;Type&#39; = type_name(xusertype),
&#39;Length&#39; = length,
&#39;Prec&#39; = case when type_name(xtype) = &#39;uniqueidentifier&#39; then xprec
else OdbcPrec(xtype, length, xprec) end,
&#39;Scale&#39; = OdbcScale(xtype,xscale),
&#39;Param_order&#39; = colid
from syscolumns where id = @objid
end
end

-- DISPLAY TABLE INDEXES & CONSTRAINTS
if @sysobj_type in (&#39;S &#39;,&#39;U &#39
begin
print &#39; &#39;
execute sp_objectfilegroup @objid
print &#39; &#39;
execute sp_helpindex @objname
print &#39; &#39;
execute sp_helpconstraint @objname,&#39;nomsg&#39;
end
else if @sysobj_type in (&#39;V &#39
begin
-- VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID
print &#39; &#39;
raiserror(15469,-1,-1) -- No constraints defined
print &#39; &#39;
raiserror(15470,-1,-1) --&#39;No foreign keys reference this table.&#39;
end

return (0) -- sp_help


--------------------------------------------------------> THE END.

Thanks,
clar.



------------
Randy at 11/14/2001 1:27:17 PM


I need a statement or sp that will display, for a given user database, an individual table&#39;s fieldnames, datatype, and length. Any help is appreciated.
Randy