May 4, 2012 at 4:03 am
Hey,
I work with MS SQL Server 2008R2.
1) In some code, I see this query is being used to find out identity and timestamp columns in table. Can someone explain what type 63 and 37 correspond to?
I tried searching sys.types in my db and I did not find these types.
SELECT name, colid, type
FROM testdb..syscolumns
WHERE id = OBJECT_ID('testdb..expected_file_info')
AND NOT ((type IN (63) AND status & 128 = 128) OR (type = 37))
2) Also how can one reliably identify 'identity' and 'timestamp cols'?
I know i can use the below to identify identity cols.
select table_schema + '.' + table_name + '.' + column_name, table_name, column_name, ordinal_position,
data_type
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(table_schema + '.' + table_name), column_name,'IsIdentity') = 1
order by table_name
Thanks
May 4, 2012 at 4:04 am
I looked at msdn documentation and i know it corresponds to physical storage type. I want to know what exactly that type maps to.
Thanks
May 4, 2012 at 4:06 am
Don't use syscolumns, it's been deprecated for 7 or so years now, it's only in the product for backward compat with SQL 2000 and there are much better schema views.
Use sys.columns and join to sys.types
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2012 at 5:05 am
Thanks. I switched to information schema.
May 9, 2012 at 5:28 am
Rather don't use the information schema either, unless you need code that's compatible across database platforms.
Query sys.columns and sys.types for the type information for a query. Books Online has more details on the joins and the meaning of all the columns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2012 at 7:40 am
GilaMonster (5/9/2012)
Rather don't use the information schema either, unless you need code that's compatible across database platforms...
Why are you saying that, Gail?
May 9, 2012 at 8:08 am
Jan Van der Eecken (5/9/2012)
GilaMonster (5/9/2012)
Rather don't use the information schema either, unless you need code that's compatible across database platforms...Why are you saying that, Gail?
Because they only show the standard information, they won't show things that are specific to SQL Server.
The structure and data of the information schema is governed by the ISO standard rules.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2012 at 8:44 am
GilaMonster (5/9/2012)
Jan Van der Eecken (5/9/2012)
GilaMonster (5/9/2012)
Rather don't use the information schema either, unless you need code that's compatible across database platforms...Why are you saying that, Gail?
Because they only show the standard information, they won't show things that are specific to SQL Server...
True.
May 9, 2012 at 10:49 am
sys.columns - http://msdn.microsoft.com/en-us/library/ms176106.aspx
sys.types - http://msdn.microsoft.com/en-us/library/ms188021.aspx
SELECT TOP 10
'column' = c.name
, 'datatype' = t.name
, *
FROM sys.columns c
INNER JOIN sys.types t
ON c.default_object_id = t.default_object_id
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply