Get columns that have timestamp
-
Hi,
anyone knows how to create a script to get the column/s that have timestamp.
ThanK
-
Open BOL and read about system tables: syscolumns, systypes.
_____________
Code for TallyGenerator -
I'll add to Serqiy's suggestion... there's a group of views called "INFORMATION_SCHEMA" that you might want to lookup in "Books OnLINE" (BOL). They don't always contain all the info you might ever want about a table or column, but when they do, they make life very simple.
The following code will do what you ask...
SELECT *
FROM Information_Schema.Columns
WHERE Data_Type IN ('DATETIME','SMALLDATETIME')
AND Table_Name = 'yourtablenamehere'
If you want to return ALL column names for the current DB that are DATETIME related (you called them "TIMESTAMP" which is a wee bit different in MS-SQL), just remove the line with the Table_Name criteria from the WHERE clause.
These types of system objects are very important, in some queries, especially if you're a DBA... Serqiy is absolutely correct in his recommendation of reading BOL for this info. I'll stress that you should become VERY familiar with the following...
System Tables
Information_Schema views
XType
sp_Help
Metadata Functions
System Functions
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I've been playing with Transactional Replication, and the Microsoft article: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog03.mspx is very useful for these scripts.
To list tables with timestamp columns:
select o.name from sysobjects o
where o.type = 'U' and objectproperty (o.id,'TableHasTimestamp') = 1
order by o.name
Hope this helps.
-
thank for your useful suggestions
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply