DB and tables usage information

Hi everybody!
Using SQL Server Enterprise Manager I can see general
information about DB, tables and indexes (Right click
over DB name and See option)
How can I print this information? or export it? Is there
any store procedure to get this information?
Thanks for your help.
LJ.Hi
Check out sp_help, sp_helpdb, sp_helptext in books online.
John
"LJ" <leyla.garcia@.unisabana.edu.co> wrote in message
news:07c901c34249$f638cfb0$a001280a@.phx.gbl...
> Hi everybody!
> Using SQL Server Enterprise Manager I can see general
> information about DB, tables and indexes (Right click
> over DB name and See option)
> How can I print this information? or export it? Is there
> any store procedure to get this information?
> Thanks for your help.
> LJ.sql

DB and tables information

Hi there!
Using SQL Enterprise Manager I can see some information
about DB usage and tables and indexes (right click over
DB name and View option)
How ca I print or export this information? Is there any
store procedure(s) to get this information?
Thanks!
LJLJ,
SQL Profiler is great for determining the queries used by an application.
Here's the code that EM uses to populate the tables/indexes Taskpad view:
select
sysusers.name + N'.' + sysobjects.name as ObjectName,
sysindexes.name as IndexName,
sysindexes.rows,
case indid
when 1 then 1
else 0
end as IsClusteredIndex,
sysindexes.indid,
sysobjects.name,
sysusers.name
from
sysusers, sysobjects, sysindexes
where
sysusers.uid = sysobjects.uid
and
sysindexes.id = sysobjects.id
and
sysobjects.name not like '#%'
and
OBJECTPROPERTY(sysobjects.id, N'IsMSShipped') <> 1
and
OBJECTPROPERTY(sysobjects.id, N'IsSystemTable') = 0
order by
ObjectName,
IsClusteredIndex DESC,
indexproperty(sysindexes.id, sysindexes.name, N'IsStatistics'),
IndexName
Dan Farino
Sr. Systems Engineer
Stamps.com, Inc.
news.danATstamps.com
"LJ" <leyla.garcia@.unisabana.edu.co> wrote in message
news:00f801c3424b$0f0a4ea0$a301280a@.phx.gbl...
> Hi there!
> Using SQL Enterprise Manager I can see some information
> about DB usage and tables and indexes (right click over
> DB name and View option)
> How ca I print or export this information? Is there any
> store procedure(s) to get this information?
> Thanks!
> LJ

DAY() not working with 'Left Join'?

I have two tables. Days (1-31) and dates (random dates)

If I have a query that is

Select Day, Date

From days LEFT JOIN dates ON days.Day = DAY(dates.date)

Order By Day, Date

The left join will not return all the days in days just the ones that join with dates. It returns as if I am doing and 'Inner join'. What do I need to do different?

Thanks.

ry this:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

|||That didn't seem to do anything. What was the thought behind this if you don't mind?|||

CREATE TABLE [dbo].[Dates]([Date] [datetime] NULL,

[id] [int] NULL)

INSERT INTO [Dates] ([Date],[id])VALUES('Oct 2 2006 12:00:00:000AM',1)
INSERT INTO [Dates] ([Date],[id])VALUES('Oct 4 2006 12:00:00:000AM',2)

CREATE TABLE [Days]([Day] [int] NULL)

INSERT INTO [Days] ([Day])VALUES(1)
INSERT INTO [Days] ([Day])VALUES(2)
INSERT INTO [Days] ([Day])VALUES(3)
INSERT INTO [Days] ([Day])VALUES(4)
INSERT INTO [Days] ([Day])VALUES(5)
INSERT INTO [Days] ([Day])VALUES(6)
INSERT INTO [Days] ([Day])VALUES(7)
INSERT INTO [Days] ([Day])VALUES(8)
INSERT INTO [Days] ([Day])VALUES(9)
INSERT INTO [Days] ([Day])VALUES(10)
INSERT INTO [Days] ([Day])VALUES(11)
INSERT INTO [Days] ([Day])VALUES(12)
INSERT INTO [Days] ([Day])VALUES(13)
INSERT INTO [Days] ([Day])VALUES(14)
INSERT INTO [Days] ([Day])VALUES(15)
INSERT INTO [Days] ([Day])VALUES(16)
INSERT INTO [Days] ([Day])VALUES(17)
INSERT INTO [Days] ([Day])VALUES(18)
INSERT INTO [Days] ([Day])VALUES(19)
INSERT INTO [Days] ([Day])VALUES(20)
INSERT INTO [Days] ([Day])VALUES(21)
INSERT INTO [Days] ([Day])VALUES(22)
INSERT INTO [Days] ([Day])VALUES(23)
INSERT INTO [Days] ([Day])VALUES(24)
INSERT INTO [Days] ([Day])VALUES(25)
INSERT INTO [Days] ([Day])VALUES(26)
INSERT INTO [Days] ([Day])VALUES(27)
INSERT INTO [Days] ([Day])VALUES(28)
INSERT INTO [Days] ([Day])VALUES(29)
INSERT INTO [Days] ([Day])VALUES(30)
INSERT INTO [Days] ([Day])VALUES(31)

And the script that works:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

If you cannot run this, let's see what is the problem again.

|||

So I get to playing around with your example and descovered some stuff I didn't know about left joins.

My query has touble when I add a Where clause on it to filter dates to a certain range. The differents querys are below in case someone else needs help. Thanks.

NOT WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date)

Where b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date) and

b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

|||

If you use a subquery with a where clause fro your LEFT JOIN, it should work.

Select a.Day, b.date

From days a LEFT JOIN (select * FROM dateshiftcrewTable Where date Between '1/1/1999' and '4/4/1999') b ON a.Day = DAY(b.date)

Order By a.Day, b.date

Datetime Filter

For one of the tables on my report i need to allow only the records where a
certain datetime field has Null value. I have not been able to figure out the
expression in the filter that would do this.
Any ideas?
I cannot do this in the select statement as other data regions require
records with values in the same field.You can do a filter at the table level (the Table report object, that is) or
at the list level by high-lighting the Table or List (for instance) and
selecting the filters from the Properties menu and placing a filter on the
data in the object. Hope this helps.
"Nice_Out" wrote:
> For one of the tables on my report i need to allow only the records where a
> certain datetime field has Null value. I have not been able to figure out the
> expression in the filter that would do this.
> Any ideas?
> I cannot do this in the select statement as other data regions require
> records with values in the same field.|||Thanks. I have figured out that I can do a filter.
What I am having trouble with is the expression to use in the filter. I get
the feeling that it does not like to handle Nulls.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> You can do a filter at the table level (the Table report object, that is)
or
> at the list level by high-lighting the Table or List (for instance) and
> selecting the filters from the Properties menu and placing a filter on the
> data in the object. Hope this helps.
> "Nice_Out" wrote:
> > For one of the tables on my report i need to allow only the records
where a
> > certain datetime field has Null value. I have not been able to figure
out the
> > expression in the filter that would do this.
> >
> > Any ideas?
> >
> > I cannot do this in the select statement as other data regions require
> > records with values in the same field.|||Sorry about that. If I had read more closely I would have seen that. Have
you tried the following in your filter?
set Expression to your date/time field
set Operator to "="
set Value to "= Nothing"
This is assuming you do not also need to include a date spread for when the
field does have a value. If so, let me know. I have that figured out as
well.
"Nice_Out" wrote:
> Thanks. I have figured out that I can do a filter.
> What I am having trouble with is the expression to use in the filter. I get
> the feeling that it does not like to handle Nulls.
> "Rand" <Rand@.discussions.microsoft.com> wrote in message
> news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > You can do a filter at the table level (the Table report object, that is)
> or
> > at the list level by high-lighting the Table or List (for instance) and
> > selecting the filters from the Properties menu and placing a filter on the
> > data in the object. Hope this helps.
> >
> > "Nice_Out" wrote:
> >
> > > For one of the tables on my report i need to allow only the records
> where a
> > > certain datetime field has Null value. I have not been able to figure
> out the
> > > expression in the filter that would do this.
> > >
> > > Any ideas?
> > >
> > > I cannot do this in the select statement as other data regions require
> > > records with values in the same field.
>
>|||Hey thanks!
That worked groovy.
I swear I tried everything so close to that. Don't know how I missed it.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DCE9E96F-CD79-4D43-B03A-88204095C6C0@.microsoft.com...
> Sorry about that. If I had read more closely I would have seen that.
Have
> you tried the following in your filter?
> set Expression to your date/time field
> set Operator to "="
> set Value to "= Nothing"
> This is assuming you do not also need to include a date spread for when
the
> field does have a value. If so, let me know. I have that figured out as
> well.
>
> "Nice_Out" wrote:
> > Thanks. I have figured out that I can do a filter.
> > What I am having trouble with is the expression to use in the filter. I
get
> > the feeling that it does not like to handle Nulls.
> >
> > "Rand" <Rand@.discussions.microsoft.com> wrote in message
> > news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > > You can do a filter at the table level (the Table report object, that
is)
> > or
> > > at the list level by high-lighting the Table or List (for instance)
and
> > > selecting the filters from the Properties menu and placing a filter on
the
> > > data in the object. Hope this helps.
> > >
> > > "Nice_Out" wrote:
> > >
> > > > For one of the tables on my report i need to allow only the records
> > where a
> > > > certain datetime field has Null value. I have not been able to
figure
> > out the
> > > > expression in the filter that would do this.
> > > >
> > > > Any ideas?
> > > >
> > > > I cannot do this in the select statement as other data regions
require
> > > > records with values in the same field.
> >
> >
> >

DateTime calculation

I have two tables "publicholidays" and "RegDate". "publicholidays" simply
stores all the date of a public holidays in a particular year. RegDate
stores the date a user is being registered. I need to perform a calculation
that will calculate the number of days the user has been registered (exclude
publicholidays) and store the result in another field in RegDate table.
Put it simply:
TotalDays = TodayDate-DateRegistered-Number of PublicHolidays in between
Date registered and Todays Date
I need to implement this as store procedure. How do I perform such task ?
Thanks.SELECT DATEDIFF(d,DateRegistered, CURRENT_TIMESTAMP) - (SELECT COUNT *
FROM PublicHolidays
WHERE HolidayDate BETWEEN O.DataRegistered AND
CURRENT_TIMESTAMP) AS TotalDays
FROM YourTable O
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"quest" <anonymous@.microsoft.com> wrote in message
news:%23F2BLpzjFHA.1504@.TK2MSFTNGP10.phx.gbl...
>I have two tables "publicholidays" and "RegDate". "publicholidays" simply
> stores all the date of a public holidays in a particular year. RegDate
> stores the date a user is being registered. I need to perform a
> calculation
> that will calculate the number of days the user has been registered
> (exclude
> publicholidays) and store the result in another field in RegDate table.
> Put it simply:
> TotalDays = TodayDate-DateRegistered-Number of PublicHolidays in between
> Date registered and Todays Date
> I need to implement this as store procedure. How do I perform such task ?
> Thanks.
>|||Thanks. Is it possible to update a field in the same table ("O" in this
case) with the new calculated value ? I tried to do update but don't seem to
get the syntax right.
Thanks again.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uMHekw0jFHA.1416@.TK2MSFTNGP09.phx.gbl...
> SELECT DATEDIFF(d,DateRegistered, CURRENT_TIMESTAMP) - (SELECT COUNT *
> FROM PublicHolidays
> WHERE HolidayDate BETWEEN O.DataRegistered AND
> CURRENT_TIMESTAMP) AS TotalDays
> FROM YourTable O
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "quest" <anonymous@.microsoft.com> wrote in message
> news:%23F2BLpzjFHA.1504@.TK2MSFTNGP10.phx.gbl...
?
>|||Try this
UPDATE YourTable
SET TotalDays = SELECT DATEDIFF(d,DateRegistered, CURRENT_TIMESTAMP) -
(SELECT COUNT *
FROM PublicHolidays
WHERE HolidayDate BETWEEN YourTable .DataRegistered AND
CURRENT_TIMESTAMP)
WHERE TotalDays IS NULL
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"quest" <anonymous@.microsoft.com> wrote in message
news:OFdHHkAkFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks. Is it possible to update a field in the same table ("O" in this
> case) with the new calculated value ? I tried to do update but don't seem
> to
> get the syntax right.
> Thanks again.
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:uMHekw0jFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ?
>|||hi
just try this
UPDATE RegDate
SEt Regdate.Column = DATEDIFF(d,DateRegistered, CURRENT_TIMESTAMP) -
count(HolidayDate)
FROM RegDate
INNER JOIN PublicHolidays PH ON
HolidayDate BETWEEN O.DataRegistered AND CURRENT_TIMESTAMP
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***

Datetime - Regional Settings

Hi,

I am using an ODBC connection with Visual Interdev 6.0 to connect to a SQL datbase and display SQL tables on a website. I am having difficulty displaying the datetime in the correct format.

It appears that the datetime is being displayed in a format that is independent of the REGIONAL settings on either the client or the server! If I change the regional settings on either machine, the datetime is still displayed in the same format.

Any ideas as to how to solve this much appreciated!::I am using an ODBC connection with Visual Interdev 6.0 to connect to a SQL datbase and
::display SQL tables on a website

These problems could be rlated to our understanding of the stuff you are using. Basically, you seem, in addition, not to be aware of the difference between ASP and ASP.NET.

ASP.NET is the successor of ASP - and has nothing in common, codewise and usage wise.

THESE forums HERE are talking about ASP.NET.

I suggest you take your ASP question to news.microsoft.com - into the ASP groups there.

Getting a sensible answer ehere would be luck - I personally have stoopped working with ASP and Interdev yeawars ago ahd hardly remember anything from these hard times.|||Pass the date pre-formatted as a string to the front-end.

Dates

Hi,

I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ReservationsConnectionString %>"

SelectCommand="SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = @.TIM_Time)">

<SelectParameters>

<asp:ControlParameter ControlID="Calendar1" Name="TIM_Time" PropertyName="SelectedDate"

Type="DateTime" />

</SelectParameters>

I get no records when I know they are there - do I have to put in additional checks to cater for the time component of the column?

Thanks in advance.

Assuming you can guarantee no time value on the @.TIM_Time parameter

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From]
FROM [Times]
WHERE ([TIM_Time] >= @.TIM_Time and [TIM_Time] < dateadd(day,1,@.TIM_Time))">

This gets all times from midnight on the day, to anything before midnight on the next day.

<light advice>I would seriously reconsider your naming convention of including a table prefix for every column, especially one that is an abbreviation. That will get seriously old over time having to type SOMETHING_ in front of every column, and then having to remove it with an alias everytime you want to display it to a user. </light advice>

|||

If TIM_Time contain only date (without time) then you can use this code

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = convert(varchar, @.TIM_Time, 112)

|||

Thaks for this but how does one hold only a date?

Is there a simple way of selecting the date part and the time part?

|||

you also can use Functions for this. You can do it in T-SQL (see following code), or write an equivalent with code behind.

declare @.dt as datetime, @.tm as datetime

select @.dt = getdate(), @.tm = getdate()

select @.tm, @.dt

select @.dt = dbo.FN_DATETIME_AS_HMS(@.dt)
select @.tm = dbo.FN_DATETIME_AS_DATE(@.tm)

select @.dt, @.tm

Here the functions code :

CREATE FUNCTION FN_DATETIME_AS_HMS (@.DT DATETIME)
RETURNS CHAR(8) AS
BEGIN
IF @.DT IS NULL RETURN NULL
DECLARE @.H INT
DECLARE @.M INT
DECLARE @.S INT
SET @.H = DATEPART(HOUR, @.DT)
SET @.M = DATEPART(MINUTE, @.DT)
SET @.S = DATEPART(SECOND, @.DT)
DECLARE @.RETVAL VARCHAR(8)
IF @.H < 10
SET @.RETVAL = '0' + CAST(@.H AS CHAR(1))+':'
ELSE
SET @.RETVAL = CAST(@.H AS CHAR(2))+':'
IF @.M < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.M AS CHAR(1))+':'
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.M AS CHAR(2))+':'
IF @.S < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.S AS CHAR(1))
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.S AS CHAR(2))
RETURN CAST(@.RETVAL AS CHAR(8))
END

--

CREATE FUNCTION FN_DATETIME_AS_DATE (@.DT DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CAST(FLOOR(CAST(@.DT AS FLOAT)) AS DATETIME)
END

ps : sources http://sqlpro.developpez.com/cours/sqlserver/udf

|||

Stephane,

Many thanks for this and sorry to be so ignorant but where exactly do I place the first part of your code? I created the 2 functions but I tried to place the first part it in a method but received an error message saying that the declare statement is not valid in a method.

|||

this is T-SQL only. it was just a sample to hava a look at the result. to run this code, execute it in Query Analyzer.

the important part is :

select dbo.FN_DATETIME_AS_HMS([Put a dateTime here])

and you'll have you time, with a 'null' date, fixed to 01/01/1900 and a valid hour. You also could choos another default value for null date. Some use 01/01/1753 with SQL Server.

select dbo.FN_DATETIME_AS_DATE([Put a DateTime here])

here, you'll have a date time with your correct date, and time set to 00:00:00.

|||

Stephane,

Thanks a lot, that was very helpful - I'm getting there - slowly!

Dates

Hi,

I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ReservationsConnectionString %>"

SelectCommand="SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = @.TIM_Time)">

<SelectParameters>

<asp:ControlParameter ControlID="Calendar1" Name="TIM_Time" PropertyName="SelectedDate"

Type="DateTime" />

</SelectParameters>

I get no records when I know they are there - do I have to put in additional checks to cater for the time component of the column?

Thanks in advance.

Assuming you can guarantee no time value on the @.TIM_Time parameter

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From]
FROM [Times]
WHERE ([TIM_Time] >= @.TIM_Time and [TIM_Time] < dateadd(day,1,@.TIM_Time))">

This gets all times from midnight on the day, to anything before midnight on the next day.

<light advice>I would seriously reconsider your naming convention of including a table prefix for every column, especially one that is an abbreviation. That will get seriously old over time having to type SOMETHING_ in front of every column, and then having to remove it with an alias everytime you want to display it to a user. </light advice>

|||

If TIM_Time contain only date (without time) then you can use this code

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = convert(varchar, @.TIM_Time, 112)

|||

Thaks for this but how does one hold only a date?

Is there a simple way of selecting the date part and the time part?

|||

you also can use Functions for this. You can do it in T-SQL (see following code), or write an equivalent with code behind.

declare @.dt as datetime, @.tm as datetime

select @.dt = getdate(), @.tm = getdate()

select @.tm, @.dt

select @.dt = dbo.FN_DATETIME_AS_HMS(@.dt)
select @.tm = dbo.FN_DATETIME_AS_DATE(@.tm)

select @.dt, @.tm

Here the functions code :

CREATE FUNCTION FN_DATETIME_AS_HMS (@.DT DATETIME)
RETURNS CHAR(8) AS
BEGIN
IF @.DT IS NULL RETURN NULL
DECLARE @.H INT
DECLARE @.M INT
DECLARE @.S INT
SET @.H = DATEPART(HOUR, @.DT)
SET @.M = DATEPART(MINUTE, @.DT)
SET @.S = DATEPART(SECOND, @.DT)
DECLARE @.RETVAL VARCHAR(8)
IF @.H < 10
SET @.RETVAL = '0' + CAST(@.H AS CHAR(1))+':'
ELSE
SET @.RETVAL = CAST(@.H AS CHAR(2))+':'
IF @.M < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.M AS CHAR(1))+':'
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.M AS CHAR(2))+':'
IF @.S < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.S AS CHAR(1))
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.S AS CHAR(2))
RETURN CAST(@.RETVAL AS CHAR(8))
END

--

CREATE FUNCTION FN_DATETIME_AS_DATE (@.DT DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CAST(FLOOR(CAST(@.DT AS FLOAT)) AS DATETIME)
END

ps : sources http://sqlpro.developpez.com/cours/sqlserver/udf

|||

Stephane,

Many thanks for this and sorry to be so ignorant but where exactly do I place the first part of your code? I created the 2 functions but I tried to place the first part it in a method but received an error message saying that the declare statement is not valid in a method.

|||

this is T-SQL only. it was just a sample to hava a look at the result. to run this code, execute it in Query Analyzer.

the important part is :

select dbo.FN_DATETIME_AS_HMS([Put a dateTime here])

and you'll have you time, with a 'null' date, fixed to 01/01/1900 and a valid hour. You also could choos another default value for null date. Some use 01/01/1753 with SQL Server.

select dbo.FN_DATETIME_AS_DATE([Put a DateTime here])

here, you'll have a date time with your correct date, and time set to 00:00:00.

|||

Stephane,

Thanks a lot, that was very helpful - I'm getting there - slowly!