September 30, 2007 at 4:10 am
I want to check the description of the table.
which one is the best way?
sp_help table_name or anyother one ?
I only want to know column names, type and size. sp_help gives lots of details which I don't want.
September 30, 2007 at 8:09 am
Open Query Analyzer...
Press f8...
Drill down to table and "columns"...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2007 at 10:31 am
Try
information_schema.columns
or syscolumns (a bit more difficult to interpret)
you can choose the columns to return.
Cursors never.
DTS - only when needed and never to control.
September 30, 2007 at 2:56 pm
October 1, 2007 at 12:23 am
Somewhat like sp_help table_name but more straight forward
sp_mshelpcolumns table_name
e.g.
sp_mshelpcolumns 'Data_table'
"-=Still Learning=-"
Lester Policarpio
October 1, 2007 at 1:16 am
Lester
sp_mshelpcolumn 'Data_table' gives me the following error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_helpcolumn'.
NigelRivett
I don't know how to use syscolumns. Please give example.
October 1, 2007 at 1:35 am
ckmoied (10/1/2007)
Lestersp_mshelpcolumn 'Data_table' gives me the following error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_helpcolumn'.
NigelRivett
I don't know how to use syscolumns. Please give example.
you type sp_helpcolumn it should be sp_mshelpcolumns then table name
"-=Still Learning=-"
Lester Policarpio
October 1, 2007 at 2:40 am
ckmoied (10/1/2007)
...
I don't know how to use syscolumns. Please give example.
syscolumns contains a row for every column/paramter in your database.
select * from syscolumns
In syscolumns you get the identifier of the tables, views, functions, etc. This identifier is in the id column
so to get the name of the object you can either join this table with sys.objects or use the object_name() function:
select object_name(id), * from syscolumns
similarly you can get information about the owner of the object to which the column belongs to, its defaults (syscomments), types (see systypes), ...
Regards,
Andras
October 4, 2007 at 7:42 pm
SchemaToDoc (http://www.schematodoc.com) has an user interface for the extended properties (descriptions) of a database's tables and fields. Open the program's Annotate screen and you will see a tree listing all of the database's tables and their fields. Click on a table or column name and you see its MS_Description extended property. You can simply view a description or edit it. You can then create a Word or HTML document that includes your annotations.
October 5, 2007 at 7:35 am
I've always used sp_columns table_name. I see that sp_mshelpcolumns table_name is very similar.
October 5, 2007 at 7:48 am
I wrote a documenter that will do this for you also - see link in my sig.
---------------------------------------
elsasoft.org
October 6, 2007 at 1:03 pm
Ok... if this is what you really want...
I only want to know column names, type and size. sp_help gives lots of details which I don't want.
... then make a function that looks like this (I appologize for the format... leading spaces are stripped off and the code window triple spaces code... took the lesser of two evils)...
CREATE FUNCTION dbo.DescribeTable
(
@TableName SYSNAME
)
RETURNS @TableInfo TABLE
(
RowNum INTEGER IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ColID INTEGER,
ColName SYSNAME,
DataType NVARCHAR(50),
AutoPopulate VARCHAR(30),
Nulls VARCHAR(8),
Description NVARCHAR(1000)
)
AS
BEGIN
INSERT INTO @TableInfo
(ColID, ColName, DataType, AutoPopulate, Nulls, Description)
SELECT ColID = STR(col.ColID,5),
ColName = col.Name,
DataType = UPPER(typ.Name)
+ CASE
WHEN dtyp.Create_Params IS NULL
THEN ''
WHEN dtyp.Create_Params = 'Precision,Scale'
THEN '('+LTRIM(STR(col.XPrec))+','+LTRIM(STR(col.XScale))+')'
WHEN dtyp.Create_Params IN ('Max Length','Length')
THEN '('+LTRIM(STR(col.Length))+')'
ELSE '(Unknown)'
END,
AutoPopulate = CASE
WHEN com.ID IS NOT NULL
THEN 'DEFAULT ' + com.Text
WHEN col.ColStat & 1 = 1
THEN 'IDENTITY('+LTRIM(STR(IDENT_SEED(obj.Name)))+','+LTRIM(STR(IDENT_INCR(obj.Name)))+')'
WHEN col.IsComputed = 1
THEN 'COMPUTED'
ELSE ''
END,
Nulls = CASE
WHEN col.IsNullable = 0 THEN 'NOT NULL'
ELSE ''
END,
Description = CAST(ISNULL(prop.Value,'') AS NVARCHAR(1000))
FROM dbo.SysProperties prop WITH (NOLOCK)
RIGHT JOIN Master.dbo.spt_DataType_Info dtyp WITH (NOLOCK)
INNER JOIN dbo.SysObjects obj WITH (NOLOCK)
INNER JOIN dbo.SysColumns col WITH (NOLOCK)
ON obj.id = col.id
INNER JOIN dbo.SysTypes typ WITH (NOLOCK)
ON col.xusertype = typ.xusertype
ON dtyp.ss_dtype = typ.xtype
LEFT JOIN dbo.SysComments com
ON col.cdefault = com.id AND com.colid = 1
ON prop.id = col.id AND prop.smallid = col.colid AND prop.name = 'MS_Description'
WHERE (dtyp.ODBCVer IS NULL OR dtyp.ODBCVer = 2)
AND (dtyp.Auto_Increment IS NULL OR dtyp.Auto_Increment = 0)
AND obj.Name = @TableName
ORDER BY col.Name
RETURN
END
GO
And then you can use it like this...
SELECT *
FROM dbo.DescribeTable('sometablename')
Or, you could turn it into a sproc...
CREATE PROCEDURE dbo.DescTable
(
@TableName SYSNAME
)
AS
SELECT ColID = STR(col.ColID,5),
ColName = col.Name,
DataType = UPPER(typ.Name)
+ CASE
WHEN dtyp.Create_Params IS NULL
THEN ''
WHEN dtyp.Create_Params = 'Precision,Scale'
THEN '('+LTRIM(STR(col.XPrec))+','+LTRIM(STR(col.XScale))+')'
WHEN dtyp.Create_Params IN ('Max Length','Length')
THEN '('+LTRIM(STR(col.Length))+')'
ELSE '(Unknown)'
END,
AutoPopulate = CASE
WHEN com.ID IS NOT NULL
THEN 'DEFAULT ' + com.Text
WHEN col.ColStat & 1 = 1
THEN 'IDENTITY('+LTRIM(STR(IDENT_SEED(obj.Name)))+','+LTRIM(STR(IDENT_INCR(obj.Name)))+')'
WHEN col.IsComputed = 1
THEN 'COMPUTED'
ELSE ''
END,
Nulls = CASE
WHEN col.IsNullable = 0 THEN 'NOT NULL'
ELSE ''
END,
Description = ISNULL(prop.Value,'')
FROM dbo.SysProperties prop WITH (NOLOCK)
RIGHT JOIN Master.dbo.spt_DataType_Info dtyp WITH (NOLOCK)
INNER JOIN dbo.SysObjects obj WITH (NOLOCK)
INNER JOIN dbo.SysColumns col WITH (NOLOCK)
ON obj.id = col.id
INNER JOIN dbo.SysTypes typ WITH (NOLOCK)
ON col.xusertype = typ.xusertype
ON dtyp.ss_dtype = typ.xtype
LEFT JOIN dbo.SysComments com
ON col.cdefault = com.id AND com.colid = 1
ON prop.id = col.id AND prop.smallid = col.colid AND prop.name = 'MS_Description'
WHERE (dtyp.ODBCVer IS NULL OR dtyp.ODBCVer = 2)
AND (dtyp.Auto_Increment IS NULL OR dtyp.Auto_Increment = 0)
AND obj.Name = @TableName
ORDER BY col.Name
GO
... and call it like this...
DescTable sometablename
OR!!! You could press F8 in the Query Analyzer window like I first suggested 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 6, 2007 at 1:05 pm
jezemine (10/5/2007)
I wrote a documenter that will do this for you also - see link in my sig.
Heh... nothing like a little shameless spam, eh Jeze?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply