Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

help needed with Microsoft SQL Server syntax

Posted on 2011-03-14
6
Medium Priority
?
273 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Jimmy_inc
  • 3
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 1880 total points
ID: 35135482
SELECT DISTINCT A.TABLE_NAME, LEFT(el.ColList, LEN(el.ColList) - 1) ColList
FROM Information_Schema.COLUMNS A
CROSS APPLY (
	SELECT COLUMN_NAME + ', ' AS [text()]
	FROM Information_Schema.COLUMNS B
	WHERE TABLE_NAME = A.TABLE_NAME
		AND DATA_TYPE IN ('datetime','smalldatetime')
	FOR XML PATH('')
	) el (ColList)
WHERE DATA_TYPE IN ('datetime','smalldatetime')

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35135563
What is your SQL version?
0
 

Author Comment

by:Jimmy_inc
ID: 35154057
SQL 2000

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35154244
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_NAME

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 120 total points
ID: 35154340
JoeNuvo -  very nice. The only change I would suggest is - instead of varchar(max), it should be something like varchar(4000). As the asker is working on sql 2000, varchar(max) won't be recognized.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35154395
Sharath_123 : thank you for your comment. I forgot about that. (since I code in on version 2005, there is no error raise)

:)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question