SQL Server Performance Forum – Threads Archive
Syscolumns
How can you tell which columns in a database can told text data? I know you can look at information_schema.values. How about syscolumns tables? I see a field called xtype. Will that tell me? I’m running SQL 2K. Thanks RinuFrom BOL: syscolumns
Contains one row for every column in every table and view, and a row for each parameter in a stored procedure. This table is in each database. xtype tinyint Physical storage type from systypes. systypes
Contains one row for each system-supplied and each user-defined data type. This table is stored in each database. xtype tinyint Physical storage type. sysobjects
Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object. xtype Char(2) Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
When I looked at the syscolumns table I saw the value 231 in the xtype column. When I looked at the corresponding columns they were nvarchar. I guess my question is there somewhere where I can find the the values for each datatype (i.e. nvarchar – 231)? Rinu
I found what I was looking for: 34 – Image
35 – Text
48 – TinyInt
52 – SmallInt
56 – Int
58 – SmallDateTime
59 – Real
60 – Money
61 – DateTime
62 – Float
99 – NText
104 – Bit
106 – Decimal
122 – SmallMoney
127 – BigInt
165 – VarBinary
167 – VarChar
173 – Binary
175 – Char
231 – NVarChar
239 – NChar
Rinu
This will show you the data types and values select name,xtype from systypes order by xtype
Madhivanan Failing to plan is Planning to fail
SP_COLUMNS also give half of the information which returns for all the columns in the table.
You can use sp_columns_ex also for specific columns. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Just a note about the TEXT data type in general. This is a quote from the upcoming SQL Server 2005 BOL.
quote:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large Value Data Types.
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large Value Data Types.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Frank, then what are the maximum sizes for those datatypes in SQL Server 2005?
Madhivanan Failing to plan is Planning to fail
Slightly less than what a TEXT data type can handle now. AFAIK, it is just one power less. So for a VARCHAR(MAX) maximum capacity would be 2^30-1 instead of 2^31-1 which is now the limit for a TEXT column. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
quote:Originally posted by rinuv
How can you tell which columns in a database can told text data?
declare @tab_name sysname, @col_name sysname
set @tab_name=’tt’
set @col_name=’cc’ select type_name(c.xusertype),*
from syscolumns c
where collation is not null –columns with character data type have collations
and id=object_id(@tab_name) and [email protected]_nameselect
If you need columns, which values should be quoted, for example, with QUOTENAME(‘abc’,””)), then query differs a bit:
select type_name(c.xusertype),*
from syscolumns c
where (collation is not null or type_name(c.xusertype)=’datetime’)
and id=object_id(@tab_name) and [email protected]_name
]]>