help needed with Microsoft SQL Server syntax
loop through all tables in database and finding date, datetime fields, returning : tablename, date1, date2 - the order of the dates does not matter e.g:
table1 deliverydate
table2 deliverydate, orderdate, invoicedate
table3 invoicedate, cutoffdate
table1 deliverydate
table2 deliverydate, orderdate, invoicedate
table3 invoicedate, cutoffdate
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sharath S
What is your SQL version?
ASKER
SQL 2000
then how about this one?
DECLARE @Temp TABLE (rid int IDENTITY (1,1) PRIMARY KEY, TABLE_NAME sysname, COLUMN_NAME sysname, LISTS varchar(max))
DECLARE @tb sysname, @lists varchar(max)
INSERT INTO @temp (TABLE_NAME, COLUMN_NAME, LISTS)
SELECT TABLE_NAME, COLUMN_NAME, ''
FROM Information_Schema.COLUMNS
WHERE DATA_TYPE IN ('datetime','smalldatetime')
ORDER BY TABLE_NAME, ORDINAL_POSITION
UPDATE @Temp
SET @lists = CASE @tb WHEN TABLE_NAME THEN @lists + ', ' + COLUMN_NAME ELSE ', ' + COLUMN_NAME END,
LISTS = @lists,
@tb = TABLE_NAME
SELECT TABLE_NAME, MAX(SUBSTRING(LISTS,3,LEN(LISTS))) ColLists
FROM @Temp
GROUP BY TABLE_NAMESOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sharath_123 : thank you for your comment. I forgot about that. (since I code in on version 2005, there is no error raise)
:)
:)