query/read database size?

I have a web-based admin section for a site and I would like to be able to
query the SQL Server database size and display it within my admin area. Is
there a function or method of doing this? My database is hosted by a third
party...

I appreciate any tips or advice you can provide!

Rob"Rob Wahmann" <dotcomstudio@.sbcglobal.net> wrote in message
news:bWdVa.27537$BM.8963811@.newssrv26.news.prodigy .com...
> I have a web-based admin section for a site and I would like to be able to
> query the SQL Server database size and display it within my admin area. Is
> there a function or method of doing this? My database is hosted by a third
> party...
> I appreciate any tips or advice you can provide!
> Rob

Depending on what information you need, sp_helpdb might be good enough:

exec sp_helpdb MyDB

Simon|||Re: exec sp_helpdb MyDB

Thanks, Simon. I've seen a lot of those commands thrown around but how do I
actually run that? I'm pretty new to SQL Server and I've never set up a
stored procedure or a trigger... I'm doing everything right now with queries
and an ODBC connection. These sites are relatively small but I do need to
learn this stuff so I can build more robust apps.

Thanks!

Rob

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f256df9$1_2@.news.bluewin.ch...
> "Rob Wahmann" <dotcomstudio@.sbcglobal.net> wrote in message
> news:bWdVa.27537$BM.8963811@.newssrv26.news.prodigy .com...
> > I have a web-based admin section for a site and I would like to be able
to
> > query the SQL Server database size and display it within my admin area.
Is
> > there a function or method of doing this? My database is hosted by a
third
> > party...
> > I appreciate any tips or advice you can provide!
> > Rob
> Depending on what information you need, sp_helpdb might be good enough:
> exec sp_helpdb MyDB
> Simon|||"Rob Wahmann" <dotcomstudio@.sbcglobal.net> wrote in message
news:ebeVa.27550$BM.8967078@.newssrv26.news.prodigy .com...
> Re: exec sp_helpdb MyDB
> Thanks, Simon. I've seen a lot of those commands thrown around but how do
I
> actually run that? I'm pretty new to SQL Server and I've never set up a
> stored procedure or a trigger... I'm doing everything right now with
queries
> and an ODBC connection. These sites are relatively small but I do need to
> learn this stuff so I can build more robust apps.
> Thanks!
> Rob

<snip
Any procedure beginning with sp_ is a system stored procedure - most are in
the master database (some are in msdb) but you can execute the ones in
master from any database on the server. I don't know much about ODBC, but if
you're already passing queries to the server and getting results, then try
to just pass the query text "exec sp_helpdb MyDB" (without the quotes, of
course), and handle the results like any other query.

One thing to be aware of is that stored procedures may return multiple
result sets, so you have to parse each result set to get all the
information. sp_helpdb returns two result sets. Books Online is an excellent
reference for all the system stored procedures - they are all listed under
the "System Stored Procedures" topic (at least assuming you have SQL2000 -
you didn't mention your version).

Simon|||Thanks again! I'm going to toy around with how to call the stored proc.

Regards,

Rob

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f257215_4@.news.bluewin.ch...
> "Rob Wahmann" <dotcomstudio@.sbcglobal.net> wrote in message
> news:ebeVa.27550$BM.8967078@.newssrv26.news.prodigy .com...
> > Re: exec sp_helpdb MyDB
> > Thanks, Simon. I've seen a lot of those commands thrown around but how
do
> I
> > actually run that? I'm pretty new to SQL Server and I've never set up a
> > stored procedure or a trigger... I'm doing everything right now with
> queries
> > and an ODBC connection. These sites are relatively small but I do need
to
> > learn this stuff so I can build more robust apps.
> > Thanks!
> > Rob
> <snip>
> Any procedure beginning with sp_ is a system stored procedure - most are
in
> the master database (some are in msdb) but you can execute the ones in
> master from any database on the server. I don't know much about ODBC, but
if
> you're already passing queries to the server and getting results, then try
> to just pass the query text "exec sp_helpdb MyDB" (without the quotes, of
> course), and handle the results like any other query.
> One thing to be aware of is that stored procedures may return multiple
> result sets, so you have to parse each result set to get all the
> information. sp_helpdb returns two result sets. Books Online is an
excellent
> reference for all the system stored procedures - they are all listed under
> the "System Stored Procedures" topic (at least assuming you have SQL2000 -
> you didn't mention your version).
> Simon|||Rob Wahmann (dotcomstudio@.sbcglobal.net) writes:
> Alrighty then... I've got the sp_helpdb prodecure returning results just
> fine but it's showing the entire size of the database + log file. Is
> there a way to specify the data file size only? I appreciate any tips!

If you specify a database name, you get two result sets, whereof the
second gives you sizes per file. You can also use sp_helpfile to get that
second result set only.

See further in Books Online. (Which you may not have installed, but see
my signature.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query with index?

Hi,

I have a table, when I execute query I want to display an index of retrieved records.

Assume table has data like:

id name phone
3 Alan1 5487411
5 Alan2 5487412
9 Alan3 5487413
10 Alan4 5487414
11 Alan5 5487415
12 Alan6 5487416

Select * from table where phone > 5487412 AND phone < 5487415

So result must be like this

index id name phone
1 9 Alan3 5487413
2 10 Alan4 5487414

Who can I get this result by SQL query?

I am not sure I understand your question; are you looking for a row number for each row returned? Something like:

declare @.mockup table
( id tinyint primary key, -- Needs to be changed
[name] varchar(7), -- Needs to be changed
phone varchar(7) -- Needs to be changed
)

insert into @.mockup values (3, 'Alan1', '5487411')
insert into @.mockup values (5, 'Alan2', '5487412')
insert into @.mockup values (9, 'Alan3', '5487413')
insert into @.mockup values (10, 'Alan4', '5487414')
insert into @.mockup values (11, 'Alan5', '5487415')
insert into @.mockup values (12, 'Alan6', '5487416')

select row_number ()
over ( order by id )
as [index],
id,
[name],
phone
from @.mockup
where phone > 5487412
and phone < 5487415

-- index id name phone
-- - - - -
-- 1 9 Alan3 5487413
-- 2 10 Alan4 5487414

Query tuning

How do you guys go about compare the efficiency of two queries?
What I usually do is run the "Display estimated execution plan" in the query
analyser and see each query cost (relative to the batch) and pick one that
gives lower percentage. Is this a good way to compare queries? Am I
missing something just by looking at that number?
Do I actually have to check IO costs, CPU running time, run the profile (and
look for what)?
Correction, tips and suggestion of best practice will be appreciated.
ThanksJustin
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
Yes , sure , as well as looking at EXECUTION PLAN of the both queries
http://www.sql-server-performance.c...performance.asp
"Justin" <nospam@.nospam.com> wrote in message
news:Oe3YBjflGHA.4244@.TK2MSFTNGP02.phx.gbl...
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the
> query analyser and see each query cost (relative to the batch) and pick
> one that gives lower percentage. Is this a good way to compare queries?
> Am I missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>|||Justin wrote:
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the que
ry
> analyser and see each query cost (relative to the batch) and pick one that
> gives lower percentage. Is this a good way to compare queries? Am I
> missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile (a
nd
> look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>
The estimated plan is a good place to start, you can identify the most
expensive parts of the query from that. You should also look at the I/O
stats and the actual execution plan. From the I/O stats, you can
identify the tables that are hit the hardest, and focus on potential
indexes, etc for those tables. Looking at the actual execution plan
will help you identify potential new indexes, improved joins, sorts, etc..

Query to only display information from one table where the foreign key doesnt exist in the

I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.

For example. If I had two tables with a one to many relationship.

A [Computer] has one or more [Hard Drives].

I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist.

This seems simple but I'm drawing a blank here.

SELECT * FROM Computer where ComputerId NOT IN (SELECT ComputerId FROM [Hard Drives])

Query to get the Domain from the email

Hi,

I wanted to get the domain name from the email in a query..
So if the email is aj70000@.hotmail.com, I just to display hotmail.com

Thanks in advance for the help.

AJ"AJ" <aj70000@.hotmail.com> wrote in message news:6097f505.0405241104.ce751b1@.posting.google.co m...
> Hi,
> I wanted to get the domain name from the email in a query..
> So if the email is aj70000@.hotmail.com, I just to display hotmail.com
> Thanks in advance for the help.
> AJ

Assume table T with relevant column email_address.

SELECT SUBSTRING(email_address,
NULLIF(CHARINDEX('@.', email_address), 0) + 1,
LEN(email_address) - CHARINDEX('@.', email_address) + 1)
AS domain_name
FROM T

--
JAG|||AJ (aj70000@.hotmail.com) writes:
> I wanted to get the domain name from the email in a query..
> So if the email is aj70000@.hotmail.com, I just to display hotmail.com

declare @.str varchar(30)
select @.str = 'aj70000@.hotmail.com'
select substring(@.str, charindex('@.', @.str) + 1, len(@.str))

I encourage you to look up the functions I used in Books Online.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query to display most active records

Hello,

I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.

There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.

I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.

For example:

PromoID StartDate EndDate
--- --- ---
PROMO1 1/1/2004 1/1/2006 (1st Active Promotion)
PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion)
PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)

Here's a query I am using to display all active promotions:

select
pl.promoid,
pr.startdate,
pr.enddate,
pl.locid,
pl.skuid,
pl.promoprice,
pl.disclevel
from
promolocation pl
inner join
promotion pr
on
pl.promoid = pr.promoid
where
pr.enddate >= getdate()

Thanks for your help.

DProvide DDL and sample data. For example, SKU is missing from your post.|||Here's the DDL:

CREATE TABLE [dbo].[Promotion] (
[PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PromoLocation] (
[PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LocID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SkuID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PromoPrice] [money] NULL ,
[DiscLevel] [float] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

And here's some of the output from my query:

promoid startdate enddate locid skuid promoprice disclevel
---- ---------------- ---------------- ------ ------ ------- ----------------
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 116 60.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 116 60.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 116 60.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 116 60.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 1 118B 99.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 2 118B 99.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 3 118B 99.0000 0.20000000000000001
20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 6 118B 99.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 119 139.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 119 139.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 119 139.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 119 139.0000 0.20000000000000001
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 1 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 2 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 3 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 4 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 6 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 7 125 135.0000 0.59999999999999998
Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 8 125 135.0000 0.59999999999999998
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 13 195.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 20 220.0000 0.20000000000000001
SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 20 220.0000 0.20000000000000001

(25 row(s) affected)|||Can somone help me with this problem?

Thanks,

D|||Can somone help me with this problem?what was the question?

:)

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John Jasper
John
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper
|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper
|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>
|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John JasperJohn
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John JasperJohn
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
--
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:
> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> > Can anyone help me with a query which will display each filed name from a
> > table along with the type of field and length ? I have tried to google
> > the
> > question with no luck
> >
> > Thanks
> >
> > John Jasper
>
>|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:
> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> > Can anyone help me with a query which will display each filed name from a
> > table along with the type of field and length ? I have tried to google
> > the
> > question with no luck
> >
> > Thanks
> >
> > John Jasper
>
>

query to display all clustered indexes

I took over here a month ago and I am finding that KEY generation is killing
one of my batch processes.
After looking at some of these tables I found that I had a CLUSTERED index
on IPAddress column. I put a stop to that one! With over 600 tables I was
wondering if there is a query to retrieve only the indexes that are
clustered and the column(s) of that index with the table it's associated
with?
That will save me a lot of time.
TIA
Hi
select tbl = object_name(i.id), i.name, c.name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
from sysindexes i
join syscolumns c on i.id = c.id
join sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
left join sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
where indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
order by tbl, i.name, k.keyno
"_Stephen" <srussell@.electracash.com> wrote in message
news:e0XOKLcTGHA.424@.TK2MSFTNGP12.phx.gbl...
>I took over here a month ago and I am finding that KEY generation is
>killing one of my batch processes.
> After looking at some of these tables I found that I had a CLUSTERED index
> on IPAddress column. I put a stop to that one! With over 600 tables I
> was wondering if there is a query to retrieve only the indexes that are
> clustered and the column(s) of that index with the table it's associated
> with?
> That will save me a lot of time.
> TIA
>
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzSw0TcTGHA.3976@.TK2MSFTNGP10.phx.gbl...
> Hi
> select tbl = object_name(i.id), i.name, c.name,
> isunique = indexproperty(i.id, i.name, 'IsUnique'),
> isclustered = indexproperty(i.id, i.name, 'IsClustered'),
> constrtype = CASE o.type
> WHEN 'PK' THEN 'PRIMARY KEY'
> WHEN 'UQ' THEN 'UNIQUE'
> END
> from sysindexes i
> join syscolumns c on i.id = c.id
> join sysindexkeys k on i.id = k.id
> and i.indid = k.indid
> and c.colid = k.colid
> left join sysobjects o ON o.name = i.name
> AND o.xtype in ('PK', 'UQ')
> AND o.parent_obj = i.id
> where indexproperty(i.id, i.name, 'IsHypothetical') = 0
> AND indexproperty(i.id, i.name, 'IsStatistics') = 0
> AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
> order by tbl, i.name, k.keyno
Fantastic!
Thanks again.

query to display all clustered indexes

I took over here a month ago and I am finding that KEY generation is killing
one of my batch processes.
After looking at some of these tables I found that I had a CLUSTERED index
on IPAddress column. I put a stop to that one! With over 600 tables I was
wondering if there is a query to retrieve only the indexes that are
clustered and the column(s) of that index with the table it's associated
with?
That will save me a lot of time.
TIAHi
select tbl = object_name(i.id), i.name, c.name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
from sysindexes i
join syscolumns c on i.id = c.id
join sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
left join sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
where indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
order by tbl, i.name, k.keyno
"_Stephen" <srussell@.electracash.com> wrote in message
news:e0XOKLcTGHA.424@.TK2MSFTNGP12.phx.gbl...
>I took over here a month ago and I am finding that KEY generation is
>killing one of my batch processes.
> After looking at some of these tables I found that I had a CLUSTERED index
> on IPAddress column. I put a stop to that one! With over 600 tables I
> was wondering if there is a query to retrieve only the indexes that are
> clustered and the column(s) of that index with the table it's associated
> with?
> That will save me a lot of time.
> TIA
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzSw0TcTGHA.3976@.TK2MSFTNGP10.phx.gbl...
> Hi
> select tbl = object_name(i.id), i.name, c.name,
> isunique = indexproperty(i.id, i.name, 'IsUnique'),
> isclustered = indexproperty(i.id, i.name, 'IsClustered'),
> constrtype = CASE o.type
> WHEN 'PK' THEN 'PRIMARY KEY'
> WHEN 'UQ' THEN 'UNIQUE'
> END
> from sysindexes i
> join syscolumns c on i.id = c.id
> join sysindexkeys k on i.id = k.id
> and i.indid = k.indid
> and c.colid = k.colid
> left join sysobjects o ON o.name = i.name
> AND o.xtype in ('PK', 'UQ')
> AND o.parent_obj = i.id
> where indexproperty(i.id, i.name, 'IsHypothetical') = 0
> AND indexproperty(i.id, i.name, 'IsStatistics') = 0
> AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
> order by tbl, i.name, k.keyno
Fantastic!
Thanks again.

query to display all clustered indexes

I took over here a month ago and I am finding that KEY generation is killing
one of my batch processes.
After looking at some of these tables I found that I had a CLUSTERED index
on IPAddress column. I put a stop to that one! With over 600 tables I was
wondering if there is a query to retrieve only the indexes that are
clustered and the column(s) of that index with the table it's associated
with?
That will save me a lot of time.
TIAHi
select tbl = object_name(i.id), i.name, c.name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
from sysindexes i
join syscolumns c on i.id = c.id
join sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
left join sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
where indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
order by tbl, i.name, k.keyno
"_Stephen" <srussell@.electracash.com> wrote in message
news:e0XOKLcTGHA.424@.TK2MSFTNGP12.phx.gbl...
>I took over here a month ago and I am finding that KEY generation is
>killing one of my batch processes.
> After looking at some of these tables I found that I had a CLUSTERED index
> on IPAddress column. I put a stop to that one! With over 600 tables I
> was wondering if there is a query to retrieve only the indexes that are
> clustered and the column(s) of that index with the table it's associated
> with?
> That will save me a lot of time.
> TIA
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzSw0TcTGHA.3976@.TK2MSFTNGP10.phx.gbl...
> Hi
> select tbl = object_name(i.id), i.name, c.name,
> isunique = indexproperty(i.id, i.name, 'IsUnique'),
> isclustered = indexproperty(i.id, i.name, 'IsClustered'),
> constrtype = CASE o.type
> WHEN 'PK' THEN 'PRIMARY KEY'
> WHEN 'UQ' THEN 'UNIQUE'
> END
> from sysindexes i
> join syscolumns c on i.id = c.id
> join sysindexkeys k on i.id = k.id
> and i.indid = k.indid
> and c.colid = k.colid
> left join sysobjects o ON o.name = i.name
> AND o.xtype in ('PK', 'UQ')
> AND o.parent_obj = i.id
> where indexproperty(i.id, i.name, 'IsHypothetical') = 0
> AND indexproperty(i.id, i.name, 'IsStatistics') = 0
> AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
> order by tbl, i.name, k.keyno
Fantastic!
Thanks again.

Query to display a field based on a parameter

I have a client table, with 8 fields, all of which contain a phone number.
Bad design, I know, but let's not get into that here. My problem now is
that in addition to those fields, we have a "Preferred" field, which simply
names the field of the preferred phone number. So we have fields like
HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
How can I create a query so that the record displays only that field that is
preferred? I hope that makes sense.
For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
Preferred "Home Phone", I want to the query to only display the name, and
home phone.
For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
"Business Phone", I want to the query to only display the name, and business
phone.
All of these records are displayed in a datagrid.
Thanks for your help.SELECT CASE Preferred
WHEN 'Home Phone' THEN HomePhone
WHEN 'Work Phone' THEN WorkPhone
..
WHEN 'Yet Another Phone' THEN YetAnotherPhone
End as PreferredPhone
FROM LotsOfPhones
Roy Harvey
Beacon Falls, CT
On Fri, 7 Apr 2006 16:29:47 -0600, "KatMagic" <SSKatMagic@.yahoo.com>
wrote:

>I have a client table, with 8 fields, all of which contain a phone number.
>Bad design, I know, but let's not get into that here. My problem now is
>that in addition to those fields, we have a "Preferred" field, which simply
>names the field of the preferred phone number. So we have fields like
>HomePhone, BusinessPhone, Fax, and the Preferred field says "HomePhone"
>How can I create a query so that the record displays only that field that i
s
>preferred? I hope that makes sense.
>For instance: John Doe, Home Phone 123-4567, Business Phone 765-4321,
>Preferred "Home Phone", I want to the query to only display the name, and
>home phone.
>For Jane Doe, Home Phone 123-4567, Business Phone 765-4321, Preferred
>"Business Phone", I want to the query to only display the name, and busines
s
>phone.
>All of these records are displayed in a datagrid.
>Thanks for your help.
>