Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Query Attraction for several Databases

Author  Topic 

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-04-08 : 16:29:54

Hi Experts,



We are having atleast 7 - 8 servers in SQL Server 2000 and 2005 and each constituting around 2- to 30 databases and each database consists with a column related to date format.

We have problem in checking the LEAP YEAR significance in each and every database of every table to verify as there is no problem with the FEBRUARY 29th, to comply with.

I need to check the query which will do for all the databases and their in-built tables.

Can anyone one help me in this regard to check the above criteria.

Thanks in advance.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-09 : 00:30:02
Sorry. Do not understand your problem. If you are using the datetime datatype you should not have any issues with leap years.

e4 d5 xd5 Nf6
Go to Top of Page

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-04-10 : 05:10:47

I want just simple query which I can Administer to find out the FREE SPACE on all databases.

Thanks again
Go to Top of Page

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-04-10 : 05:12:57

In Continuation, I just want to TEST with QUERY the Tables in all the databases with the Leap year Date like 29-02-2008.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-10 : 08:37:25
Free space has nothing to do with dates or leap years.
2008 is a leap year, so a date of 29-02-2008 is perfectly valid.
If you are using datetime datetypes, you don't need to test anything.
You are making no sense at all, so I am dropping out of this thread.

e4 d5 xd5 Nf6
Go to Top of Page

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-04-11 : 06:30:58

Thanks for dropping. Better
Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-11 : 06:41:33
well, i should think that no-one else has replied it's probably syptomatic of the fact that no-one else understands what you want either. Personally, I don't even understand the title

Em
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 07:17:37
Or maybe OP don't know what he is talking about?

1) Databases don't have columns. Tables have.
2) Related to date format? What is that? Is there, or is there not, a column containing date values? Or containg date formats?
3) In what context are you referring significance with?
4) Tables belong to databases. Databases do not belong to tables.
5) Which query do you want to check?
6) Do all databases?
7) Built-in tables? Are you referring to system tables or user tables?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-04-11 : 17:06:06

Yes Experts I have expected this was not the answer but it was natural to understand as what I expect of the answer.

Every table in my Database(s) have a Date related column means in that column if I query will return the current date or will understand the date format to be inserted or when queried will return the date.

Keeping in view of this analogue, I expected some one might help with a query which suits for the LEAP YEAR DAY i.e, 29-02-2008 will be in Where Clause will respond to that query.

Something Like this,

Select * from Table
Where currentdate = '29-02-2008' (Correct this Syntax)

but I need to query on all the tables which reside in all the databases and in all servers which accumulates me for more than 200 to 300 tables around in production environment only.

As I cannot execute the query on each and single table which might take as laborious task as any other but to have checked in generalised query or in the form of Stored procedure.

Now can anyone give such query which might help me to get the query or the SP to CHECK all my Table's Columns.

Once again thanks for your guidance if any.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 17:13:50
Rin this query
SELECT	'SELECT * FROM ' + TABLE_NAME + ' WHERE CurrentDate = ''20080229'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'CurrentDate'
Then copy and paste the result in a new query window and run the result.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-04-12 : 19:26:02
Thanks Peso for your Query, as I do have similar query.

However, I need to run on each database this query, instead any other method that I can run on entire database based all the tables residing in it.

Thanks a lot.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-12 : 22:56:28
Sure, you could write a complex dynamic query that would cycle through every database on the server, and every table in each database, and every column in each table, and execute some sort of SQL. But to be frank, this appears to be beyond your level of SQL Expertise, and such a task is really too involved and complex to expect someone to write for you for free on an internet forum.
You really need to find a skilled DBA in your area who you can pay to do this work for you.

e4 d5 xd5 Nf6
Go to Top of Page

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-04-13 : 02:02:24

I appreciate your answer which is generosity and do you consider for such jobs here.

I see the forum is based on charity and help based and I never expect this approach as this is not a laborious task since I have the script already.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-13 : 10:07:00
I don't know any DBAs in Australia, but there must be some. Check your local IT Consulting companies. Or do a google search for "DBA [YourCity]".
Where are you in Australia?

e4 d5 xd5 Nf6
Go to Top of Page

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-04-14 : 00:14:40


Thanks for you especially as you bothered for me.

I will organise for myself.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 02:36:45
I am having a hard time deconding what OP is writing.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-14 : 09:17:14
Probably because his native language is Australian. ;)

e4 d5 xd5 Nf6
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-14 : 13:02:50
quote:
Originally posted by vasanthhepsy



Thanks for you especially as you bothered for me.

I will organise for myself.



Shame on you, you bothered him!!! Reading his requests gave me a headache! Just makes you want to slap someone!

Terry
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-14 : 13:41:34
I think what he meant was "Thank you for bothering (taking the time) to help me."
I mean, I doubt that you are fluent in Australian, so cut the guy some slack, eh?

e4 d5 xd5 Nf6
Go to Top of Page
   

- Advertisement -