Comprehensive Index Information

Hi,

I am writing an in house utility to attempt to compare different
aspects of databases.
I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).

I would like the following information, in one result set if possible:

Table Name
Index Name
Column Name
Column Position
Unique?

Now on Oracle, this is easily done with the following query:

SELECT IND.TABLE_NAME, IND.INDEX_NAME, IND.COLUMN_NAME,
IND.COLUMN_POSITION, COL.UNIQUENESS
FROM USER_IND_COLUMNS IND,
USER_INDEXES COL
WHEREIND.INDEX_NAME = COL.INDEX_NAME
ORDER BY 1, 2, 3, 4, 5

I have been trying for over an hour now to get the equivalent, and I
really cannot figure it out. If anybody can come up with this then I
would greatly appreciate it!

Many Thanks,

PaulPaul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

I am writing an in house utility to attempt to compare different
aspects of databases.

Before you go too far, pay a visit to http://www.red-gate.com and
if SQL Compare meets your needs.

Quote:

Originally Posted by

I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).
>
I would like the following information, in one result set if possible:
>
Table Name
Index Name
Column Name
Column Position
Unique?

SELECT tablename = t.name, indexname = i.name,
colname = c.name, pos = ic.index_column_id,
indextype = i.type_desc, isunique = i.is_unique
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c ON t.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE i.is_hypothetical = 0

There are probably more columns should include in the output, but I
levae that as an exercise.

Note: the above works in SQL 2005 only. Next time, please specify which
version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Thanks for the link - unfortunately this is more of an exercise for
the time being and so we are not willing to spend money on a tool at
present!

Thanks for the help - if you do know something that will work on both
versions that would be good.

Paul|||Paul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Then you need to work against sysobjects, sysindexes, sysindexkeys and
syscolumns. The query will be similar, but you need to filter for
statistics, since in SQL 2000 statistics and indexes live in sysindexes.

These are documented in Books Online, and since this is an exercise for you,
I leave you there. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.

Composite primary key autoincrement from 1

Hi,

I'm currently writing a small application that is using SQL Server as a back-end database. A part of my database looks something lie the following:

Tables:
Orders(OrderId[int, PK], OrderDescription[varchar] etc...)
OrderLines(OrderId[int, PK, FK], OrderLineId[int, PK], etc...)

What I need to achieve is - everytime that a new line is inserted into an orderlines table part of the primary key will be the OrderId and the OrderLineId should be auto-incremented from 1 for each OrderId in the OrderLines table.

I know i can do this manually in my program, but i'm just wondering if theres a way to achive this in SQL Server?

Thanks,

Nick Goloborodko

Hi BushGates :-)

The two options are:

-Doing it manually within your application (like you already netioned)

-Doing it through a trigger which would evaluate the next ids for your inserted data.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens :)

Thanks for your reply - i juess i'll stick with generating the line IDs in my code :)

Nick

complex(?) query

Hello experts.

I'm a novice sql writer and need some help in writing a query to
extract applicable data from the following table (titled EMPLOYEE):

--
ID_NUMBERCODEDATE
------ --- ---
12 VO20060914
12 XD20060913
12 AD20060912
12 WR20060911
12 AT20060910
45 VO20060914
45 XR20060913
45 AT20060912
45 AD20060911
45 AT20060910
78 AD20060914
78 AT20060913
78 VO20060912
78 AD20060911
78 AT20060910

I need to select ID_NUMBER
from EMPLOYEE
where CODE = 'VO'

caveat: I only want the ID_NUMBER(s) where the CODE = 'VO'
and the previous CODE (by DATE) = 'AD'
or the previous CODE (by DATE) = 'AD' with any CODE in between
except 'AT';

E.g., in the above example, the appropriate code should select
ID_NUMBER(s) 12 and 78 because
1. a VO code exists
2. an AD code (by DATE) precedes it
3. although 'AD' does not come immediately before 'VO' (in the
case of ID_NUMBER 12) 'AT' cannot be found in between

I hope I haven't confused anyone. Any help would be appreciated.Alex,

You seem to be almost there and you just have to put it all together to
come up with the answer.
To help you along, let's talk syntax and "NOT EXISTS"

1. Are you familiar with the syntax of a join that allows you to
select a subset of data before using it in the join.
As this applies to your case:

SELECT col1, col2, col3
FROM ( SELECT col1, col2, col3
FROM codes
WHERE code = '?' ) <tbl_alias>
INNER JOIN (SELECT col1, col2, col3
FROM codes
WHERE code = '?' ) <tbl_alias2>
ON tbl_alias1.col? = tbl_alias2.col?
< Add more join conditions >

2. NOT EXISTS is useful in your example. What do you want to NOT
EXIST?

Quote:

Originally Posted by

3. although 'AD' does not come immediately before 'VO'

(in the

Quote:

Originally Posted by

case of ID_NUMBER 12) 'AT' cannot be found in between

AND NOT EXISTS ( SELECT 1 FROM codes WHERE ? )
Remember in the N/E sub_select you will be comparing values in a
correlated sub_query.

I'll check back and post the answer if you are still having difficulty
but only after you post some sql that you have been trying to make
work.

alex wrote:

Quote:

Originally Posted by

Hello experts.
>
I'm a novice sql writer and need some help in writing a query to
extract applicable data from the following table (titled EMPLOYEE):
>
--
ID_NUMBERCODEDATE
------ --- ---
12 VO20060914
12 XD20060913
12 AD20060912
12 WR20060911
12 AT20060910
45 VO20060914
45 XR20060913
45 AT20060912
45 AD20060911
45 AT20060910
78 AD20060914
78 AT20060913
78 VO20060912
78 AD20060911
78 AT20060910
>
I need to select ID_NUMBER
from EMPLOYEE
where CODE = 'VO'
>
caveat: I only want the ID_NUMBER(s) where the CODE = 'VO'
and the previous CODE (by DATE) = 'AD'
or the previous CODE (by DATE) = 'AD' with any CODE in between
except 'AT';
>
E.g., in the above example, the appropriate code should select
ID_NUMBER(s) 12 and 78 because
1. a VO code exists
2. an AD code (by DATE) precedes it
3. although 'AD' does not come immediately before 'VO' (in the
case of ID_NUMBER 12) 'AT' cannot be found in between
>
I hope I haven't confused anyone. Any help would be appreciated.

|||Alex,

Let me update my previous post.
The subselects within the join are not necessary.
It is the NOT EXISTS that you should be interested in.
You will still have to join the table with itself to filter out the
rows for VO and AD within two separate aliased tables.

alex wrote:

Quote:

Originally Posted by

Hello experts.
>
I'm a novice sql writer and need some help in writing a query to
extract applicable data from the following table (titled EMPLOYEE):
>
--
ID_NUMBERCODEDATE
------ --- ---
12 VO20060914
12 XD20060913
12 AD20060912
12 WR20060911
12 AT20060910
45 VO20060914
45 XR20060913
45 AT20060912
45 AD20060911
45 AT20060910
78 AD20060914
78 AT20060913
78 VO20060912
78 AD20060911
78 AT20060910
>
I need to select ID_NUMBER
from EMPLOYEE
where CODE = 'VO'
>
caveat: I only want the ID_NUMBER(s) where the CODE = 'VO'
and the previous CODE (by DATE) = 'AD'
or the previous CODE (by DATE) = 'AD' with any CODE in between
except 'AT';
>
E.g., in the above example, the appropriate code should select
ID_NUMBER(s) 12 and 78 because
1. a VO code exists
2. an AD code (by DATE) precedes it
3. although 'AD' does not come immediately before 'VO' (in the
case of ID_NUMBER 12) 'AT' cannot be found in between
>
I hope I haven't confused anyone. Any help would be appreciated.

|||>>I'm a novice sql writer and need some help in writing a query to extract applicable data from the following table (titled EMPLOYEE): <<

First, let's clean up your missing DDL. The table name should tell us
what set of entities is modeled in the table; do you really have one
employee? Small firm! Try Personnel -- the collective name of the set
or something that tells us what the set is. Code is too vague --
postal code? Date is both too vague *and* a reserved word. A name
like "id_number" is also uselessly general; emp_id would be a better
choice. Since you did not post DDL, we have to guess at constaints and
keys. A skeleton of what you need is something like this:

CREATE TABLE PersonnelActions
(emp_id INTEGER NOT NULL,
action_date action_dateTIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (emp_id, foobar_date),
action_code CHAR(2) NOT NULL
CHECK (foobar_code IN ('VO', 'XD'))
);

You need to read a book on data modeling and ISO-11179 rules for names.
I would also look up the use of UPPERCASE for names -- it is the worst
way to code, being about 8-12% harder to detect misspellings. That is
why books and newspapers use lowercase.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I only want the emp_id(s) where the action_code = 'VO'

and the previous action_code (by action_date) = 'AD'
or the previous action_code (by action_date) = 'AD' with any
action_code in between
except 'AT'; <<

SELECT DISTINCT emp_id
FROM PersonnelAction AS PVO,
PersonnelAction AS PAD
WHERE PVO.emp_id = PAD.emp_id
AND PVO.action_code = 'VO'
AND PAD.action_code = 'AD'
AND PAD.action_date < PVO.action_date
AND NOT EXISTS
(SELECT *
FROM PersonnelAction AS PAT
WHERE PAT.action_code = 'AT'
AND PAT.emp_id = PVO.emp_id
AND PAT_action_date BETWEEN PAD.action_date AND
PVO.action_date);|||>>I'm a novice sql writer and need some help in writing a query to extract applicable data from the following table (titled EMPLOYEE): <<

First, let's clean up your missing DDL. The table name should tell us
what set of entities is modeled in the table; do you really have one
employee? Small firm! Try Personnel -- the collective name of the set
or something that tells us what the set is. Code is too vague --
postal code? Date is both too vague *and* a reserved word. A name
like "id_number" is also uselessly general; emp_id would be a better
choice. Since you did not post DDL, we have to guess at constaints and
keys. A skeleton of what you need is something like this:

CREATE TABLE PersonnelActions
(emp_id INTEGER NOT NULL,
action_date action_dateTIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (emp_id, foobar_date),
action_code CHAR(2) NOT NULL
CHECK (foobar_code IN ('VO', 'XD'))
);

You need to read a book on data modeling and ISO-11179 rules for names.
I would also look up the use of UPPERCASE for names -- it is the worst
way to code, being about 8-12% harder to detect misspellings. That is
why books and newspapers use lowercase.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I only want the emp_id(s) where the action_code = 'VO'

and the previous action_code (by action_date) = 'AD'
or the previous action_code (by action_date) = 'AD' with any
action_code in between
except 'AT'; <<

SELECT DISTINCT emp_id
FROM PersonnelAction AS PVO,
PersonnelAction AS PAD
WHERE PVO.emp_id = PAD.emp_id
AND PVO.action_code = 'VO'
AND PAD.action_code = 'AD'
AND PAD.action_date < PVO.action_date
AND NOT EXISTS
(SELECT *
FROM PersonnelAction AS PAT
WHERE PAT.action_code = 'AT'
AND PAT.emp_id = PVO.emp_id
AND PAT_action_date BETWEEN PAD.action_date AND
PVO.action_date);|||Alex,

It looks like CELKO gave you the answer along with a lot more
"advanced" advice. I hope you appreciate that I was trying to give you
a nudge to let you figure it out.

If you are still having trouble, post your SQL and we can make any
corrections.

Bill

alex wrote:

Quote:

Originally Posted by

Hello experts.
>
I'm a novice sql writer and need some help in writing a query to
extract applicable data from the following table (titled EMPLOYEE):
>
--
ID_NUMBERCODEDATE
------ --- ---
12 VO20060914
12 XD20060913
12 AD20060912
12 WR20060911
12 AT20060910
45 VO20060914
45 XR20060913
45 AT20060912
45 AD20060911
45 AT20060910
78 AD20060914
78 AT20060913
78 VO20060912
78 AD20060911
78 AT20060910
>
I need to select ID_NUMBER
from EMPLOYEE
where CODE = 'VO'
>
caveat: I only want the ID_NUMBER(s) where the CODE = 'VO'
and the previous CODE (by DATE) = 'AD'
or the previous CODE (by DATE) = 'AD' with any CODE in between
except 'AT';
>
E.g., in the above example, the appropriate code should select
ID_NUMBER(s) 12 and 78 because
1. a VO code exists
2. an AD code (by DATE) precedes it
3. although 'AD' does not come immediately before 'VO' (in the
case of ID_NUMBER 12) 'AT' cannot be found in between
>
I hope I haven't confused anyone. Any help would be appreciated.

|||--CELKO-- wrote:

Quote:

Originally Posted by

First, let's clean up your missing DDL. The table name should tell us
what set of entities is modeled in the table; do you really have one
employee? Small firm! Try Personnel -- the collective name of the set
or something that tells us what the set is.

What about "Employees"? But this is mostly grammatical pedantry; any
reasonable person will understand the implicit plural.

Quote:

Originally Posted by

Since you did not post DDL, we have to guess at constaints and
keys. A skeleton of what you need is something like this:
>
CREATE TABLE PersonnelActions
(emp_id INTEGER NOT NULL,
action_date action_dateTIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (emp_id, foobar_date),
action_code CHAR(2) NOT NULL
CHECK (foobar_code IN ('VO', 'XD'))
);

"something like", indeed. That penultimate line should be more like
CHECK (action_code IN ('VO', 'XD', 'AD', 'WR', 'AT', 'XR'))
or should refer to an ActionCodes table, or should simply be omitted.

Quote:

Originally Posted by

SELECT DISTINCT emp_id

Need to specify whether it comes from PVO or PAD.

Quote:

Originally Posted by

FROM PersonnelAction AS PVO,
PersonnelAction AS PAD
WHERE PVO.emp_id = PAD.emp_id
AND PVO.action_code = 'VO'
AND PAD.action_code = 'AD'
AND PAD.action_date < PVO.action_date

The following is clearer IMO:

FROM PersonnelAction AS PEarlier
JOIN PersonnelAction AS PLater ON PEarlier.emp_id = PLater.emp_id
AND PEarlier.action_date < PLater.action_date
WHERE PEarlier.action_code = 'AD'
AND PLater.action_code = 'VO'

Quote:

Originally Posted by

AND NOT EXISTS
(SELECT *
FROM PersonnelAction AS PAT
WHERE PAT.action_code = 'AT'
AND PAT.emp_id = PVO.emp_id
AND PAT_action_date BETWEEN PAD.action_date AND
PVO.action_date);

complex SELECT

Hi,

I have problem about writing a proper SELECT query for the following
goal:

Table name: peoplelist
column 1: id (not NULL, auto_incremental)
column 2: name
column 3: country

now, there are about 7,000 rows in this table. I want to select out:
first 10 or less people in the table for each country.

for example: suppose there are :

1000 people from US
3000 people from UK
3000 people from Canada

I want to list totally 30 people, i.e. 10 people from each country.

The problem is , the actual table includes many countries, not only
three. How can I do this by a SELECT sql query ?

Thanks.

HanAssuming you want the 10 people from each country based on their IDs (10
lowest values), one of the following queries should do it.

CREATE TABLE People (id INTEGER PRIMARY KEY, name VARCHAR(30) NOT NULL
UNIQUE, countrycode CHAR(2) NOT NULL /* REFERENCES Countries (countrycode)
*/)

Standard SQL:

SELECT P.id, P.name, P.countrycode
FROM People AS P
WHERE id IN
(SELECT P1.id
FROM People AS P1
JOIN People AS P2
ON P1.id >= P2.id
AND P1.countrycode = P.countrycode
AND P2.countrycode = P.countrycode
GROUP BY P1.id
HAVING COUNT(*)<=10)

TSQL using TOP:

SELECT P.id, P.name, P.countrycode
FROM People AS P
WHERE id IN
(SELECT TOP 10 id
FROM People
WHERE countrycode = P.countrycode
ORDER BY id)

--
David Portas
SQL Server MVP
--

Complex Query Help.. Please :)

Hello group,

First time post..

I've designed an online evaluation script (php/mssql) and need help either writing a query to get the posted data out in a way that is useable to those collecting the evaluations..

Here are the tables.

evaluations - holds a record for each evauation that is created: evalID, evalName

fields - holds a record for each potential field that could be added to the evaluation: fieldID, fieldTitle, fieldDescription, and fieldTypeID (types are in a seperate table)

fields_evals - contains the link between the evaluations table and the fields table. this one essentially creates teh evaluation from the eval name and the list of fields. it also contains some switches that specify how the data should be reported (i.e. averaged and/or grouped by) and values that determine if the fields are required and what position they are to be listed in the evaluation form.. (hope that made sense) : evalID, FieldID, position, required, groupby, average

fields_custom - one of the field types is a custom field where the user can create a field with a list of options (i.e. Jan, Feb, Mar, April... or whatever they like) : optionID, optionText, fieldID

submitted - holds data for each individual submitted eval..: subID, timestamp, and evalID

eval_data - this is the biggie.. this is the table that all the responses are written to. it has these fields... data_ID, subID, fieldID, data

Here's the problem..
I want the report page to be able to summraize the data by grouping by the data set in the eval_fields table.. (a row for each value submitted) and on that row have all of the averaged fields (again indicated from the eval_fields table) listed with their averages.. all of the data will come from the eval_data table.. possibly including (and i think this may be the main problem) an id value that relates to the optionID in teh custom_fields table.

an example of a posted evaluation will create:
1 row in the submitted table..
and a row in the eval_data table for each field submitted..as well as

with any luck this explanation makes sense and the problem will be easy to solve..

:rolleyes:

any replys are greatly appreciated..

Thanks
Willok.. here is some clarification..

I started thinking about the first post and decided that much of that information was probably not needed..

Here is the sql i'm workin with so far..

SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText
FROM eval_eval_data AS d
INNER JOIN fields_evaluation as fe ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON f.intField_ID = d.intField_ID
INNER JOIN fields_custom as c ON f.intField_ID = c.intFieldID
WHERE fe.intAverage = '1'
AND c.intOption_ID = '26'
Group By f.chEvalFieldText

my question boils down to this..
regarding the rows in the data table..

can i group by a value in a liked table (f),
and average values in the data table
while limiting by values in teh data table as well.

anybody?

Am i on the wrong track?

Am i totally lost?

have i lost everybody else??

thanks
ws
-|||You are right on track, good job.
:D|||me again...

I'm fairly confident that there is a query that will get me what i need.. but i'm having a helluva time getting it to work..

here is some example data from the data table..

intSub intField_ID chData
1 1 1
1 2 1
1 3 4
1 4 2
1 26 27
2 1 1
2 2 2
2 3 3
2 4 5
2 26 28

intField_ID is a key that relates to fields in the evaluation
chData is the value that was submitted and intSubmission indicates which submission the data came from.
In this case the field_ids 1-4 ratings 1- 5 that will be averaged
the average will be grouped by the field ID no biggie.. here's where i'm stumped.
I Also have to average and group those values when the fieldID 26 (which is a field that was created within the application) equals a certian value.

make sense?
there is also a table that connects the fields to the evaluation and indicates which fields will be averaged..
for the data above .. i need to

Average the values for the indicated fields grouped by the field id..
but only where the value for fieldID = 26 are equal..

any help is greatly appreciated.
If you need more info.. please let me know..

thanks
will