I have to do some paging stuff. I am doing the follow in a proc.
Select top 10 * from customer
where @.SearchKey <= LastName+FirstName+CustomerNumber
Question is what index to I build on the database to be able to search
this effectively?
Thank you.
ChrisFirst don't select * - specify the columns you want returned.
Second your condition is probably always going to result in a table scan
because you're not searching columns, you're comparing 2 atomic values for
each record. One value being the variable and the other being the
concatenation of the 3 columns.
Better to:
SELECT col1, col2, colN
FROM dbo.customer
WHERE
LastName >= @.SearchKey
OR FirstName >= @.SearchKey
OR CustomerNumber >= @.SearchKey
You could then build some indexes on any or all of those 3 columns.
"Chris" wrote:
> I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris
>|||Chris,
SELECT TOP 10 <COLUMN LIST>
FROM CUSTOMER
WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
BY THIS KEY
--CREATE INDEX ON CUSTOMERNUMBER
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris|||>> Question is what index to I build on the database to be able to search
Try this and see if it makes a difference. Create a computed column like:
concat AS last_name + first_name + cust_nbr.
Then cluster the computed column like:
CREATE CLUSTERED INDEX Idx ON Customers( concat ASC )
Note that this will bias the table access for queries involving this
specific predicate and could potentially slowdown other data manipulation
operations.
Anith|||Jerry Spivey wrote:
> Chris,
> SELECT TOP 10 <COLUMN LIST>
> FROM CUSTOMER
> WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
> BY THIS KEY
> --CREATE INDEX ON CUSTOMERNUMBER
> HTH
> Jerry
> "Chris" <no@.spam.com> wrote in message
> news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>
>
>
That doesn't solve my problem. I'm trying to search for the person
alphabetically after the person I passed in. you way just scans for the
customer id.
Chris|||OK...perhaps you should say that in your original post next time.
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||Try:
CREATE PROC #APROC
@.LASTNAME VARCHAR(20)
AS
SELECT <COLUMN LIST>
FROM AUTHORS
WHERE LASTNAME > @.LASTNAME
ORDER BY LASTNAME
--CONSIDER CREATING A CLUSTERED INDEX ON LASTNAME
--DROP PROC #APROC
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||KH wrote:
> First don't select * - specify the columns you want returned.
> Second your condition is probably always going to result in a table scan
> because you're not searching columns, you're comparing 2 atomic values for
> each record. One value being the variable and the other being the
> concatenation of the 3 columns.
> Better to:
> SELECT col1, col2, colN
> FROM dbo.customer
> WHERE
> LastName >= @.SearchKey
> OR FirstName >= @.SearchKey
> OR CustomerNumber >= @.SearchKey
> You could then build some indexes on any or all of those 3 columns.
>
> "Chris" wrote:
>
This won't work.. If I have the name 'Smith, Joe 1234' your solution
will give me as long as their name is getter than Smith or have a first
name greater than joe (i.e. "Apple, Zebra" would be allowed)
Chris|||On Fri, 14 Oct 2005 16:16:20 -0400, Chris wrote:
>I have to do some paging stuff. I am doing the follow in a proc.
>Select top 10 * from customer
>where @.SearchKey <= LastName+FirstName+CustomerNumber
>Question is what index to I build on the database to be able to search
>this effectively?
>Thank you.
>Chris
Hi Chris,
Anith's suggestion to add a computed column and index it is good. But if
that's for some reason unwanted, then try the query below. The extra AND
might look redundant, but your version is unable to use an index on the
LastName column, and the redundant extra AND does enable some
preselection using that index (if there is one, that is).
SELECT TOP 10 Column1, column2, ... -- Don't use SELECT *
FROM customer
WHERE @.SearchKey <= LastName+FirstName+CustomerNumber
AND @.SearchKey <= LastName
By the way, are you aware that using TOP without ORDER BY will yield
undefined results?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you have 'Smith, Joe 1234' your solution won't work either, as (I'm
assuming your data doesn't have the spaces and commans in it) concating the
fields will yield 'SmithJoe1234' which would probably fail the condition in
most cases.
Why are you using less than operators for string comparison anyways?
You should be dealing with things like that before it gets to the database.
"Chris" wrote:
> KH wrote:
> This won't work.. If I have the name 'Smith, Joe 1234' your solution
> will give me as long as their name is getter than Smith or have a first
> name greater than joe (i.e. "Apple, Zebra" would be allowed)
> Chris
>
Showing posts with label stuff. Show all posts
Showing posts with label stuff. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Compromise?
OK, I guess there's more stuff to look for. Of the list
that you mentioned in the last thread, I did find nc.exe
but no others. There were two versions, one in System32
from about a year ago but the other was in a subfolder
and was dated recently.
I'm embarrassed about my complete lack of SQL knowledge
(I shouldn't be because I never agreed to support it.)
but let me say this....
Please tell me that there IS supposed to be a "sql_admin"
local Windows account and that it IS supposed to be in
the local Administrators group.
revealing:
very
msjet40enginev2.vxd
with
Please check the logon account configured for SQL Server. I suspect the
individual that installed SQL Server created an account called SQL_Admin
and configured SQL Server and possibly SQL Agent to use this account.
You can check this in two places. One is SQL Enterprise Manager.
Highlight the server name, right click and select properties. Then, select
the security tab. That will display among other items the account used for
the SQL Server service.
To check the account for SQL Agent, open the Mangement folder for the
server, highlight SQL Agent. Right click and select properties. You will
be able to see the account used for the SQL Agent service in that area.
Of course, you can check the credentials in the Services area of Computer
Management as well. It is recommended that if you change the account
and/or password, that you do it through SQL Enterprise Manager.
Under the concept of least privilege, you will want to take that account
out of the Administrators group for the Windows server.
Thanks.
Gary
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have already checked in Services (sticking with what I
am very familiar with) and yes, those 2 SQL related
services are running under the sql_admin account.
From what you are saying, that account does not have to
be in the local Administrators group or in any other
group (besides Users, of course). Is that correct?
As for the person who set up the account and
installed/configured SQL - Got me! Therein lies the
problem. Many faculty members get tech resources without
support. I believe a student who is no longer around set
it up! Ahh, the joys of Academic IT.
Server. I suspect the
called SQL_Admin
this account.
Manager.
properties. Then, select
the account used for
folder for the
properties. You will
service in that area.
area of Computer
change the account
Manager.
take that account
confers no rights.
with Enterprise Manager. The rights needed by the service account are
documented in this KB article:
http://support.microsoft.com/defaul...b;en-us;Q283811
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Charlie" <anonymous@.discussions.microsoft.com> wrote in message
news:024d01c3ba8d$4d274480$a101280a@.phx.gbl...[QUOTE]
> I have already checked in Services (sticking with what I
> am very familiar with) and yes, those 2 SQL related
> services are running under the sql_admin account.
> From what you are saying, that account does not have to
> be in the local Administrators group or in any other
> group (besides Users, of course). Is that correct?
> As for the person who set up the account and
> installed/configured SQL - Got me! Therein lies the
> problem. Many faculty members get tech resources without
> support. I believe a student who is no longer around set
> it up! Ahh, the joys of Academic IT.
>
> Server. I suspect the
> called SQL_Admin
> this account.
> Manager.
> properties. Then, select
> the account used for
> folder for the
> properties. You will
> service in that area.
> area of Computer
> change the account
> Manager.
> take that account
> confers no rights.|||Hi Charlie,
I reviewed the threads on this post today and realized a lot of ground has
been covered. I would like to summarize the issue if I may.
1. Your initial request concerned a question about multiple random TCP
ports on your server in communication with an external server via TCP port
1433. You were seeking help to determine if the box had been compromised.
2. Based on this information and additional information about files
located on the server, there is a chance the box has indeed been
compromised.
3. Your action at this time will depend on your objective. If you desire
to conduct a forensics examination on the server, you may want to consult
the information outlined at http://www.cert.org/nav/index_red.html in the
fixes section.
4. The following items specific to SQL Server configuration have been
discussed.
A. The 'SA' account must be secured with a very strong password.
B. The server should be shielded from the Internet by a firewall.
Specifically, TCP port 1433 should be closed for incoming packets from the
Internet.
C. Client/server communication with SQL Server is handled via incoming
packets to SQL over TCP port 1433 and outgoing packets to the clients on
one of the ports in the range 1024 to 5000. The specific port is
determined when the client connects.
D. The logon credentials for the SQL Server and SQL Agent accounts should
not be a member of the Administrators group on the server nor should they
be members of the Domain Admin group.
5. In the event you rebuild the server, SQL Server 2000 should be upgraded
to Service Pack 3a. A post SP3 security patch for SQL Server, MS03-031
should be applied. I also recommend applying MS03-033 which is a patch for
MDAC 2.7 SP1. When you upgrade SQL Server 2000 with SP3, MDAC 2.7 SP1 is
applied automatically.
6. In the event you rebuild the Windows server, please apply the
applicable Service Packs and security updates before placing the server on
the network and certainly before placing it on the Internet. The Windows
Update web site is very useful for cataloging the updates needed for a
specific build of the operating system. Also, the Microsoft Baseline
Security Analyzer is a useful tool to help you determine the patches and
configurations appropriate for your server. MBSA is available at
http://www.microsoft.com/downloads/...e63b-92e3-4f97-
80e7-8bc9ff836742&DisplayLang=en.
7. You may want to download the updated SQL Server Books On LIne. It has
been updated for SP3. This can be installed as a separate package. It is
availble at
http://www.microsoft.com/downloads/...71A6-BCF4-45A6-
A2E2-F6AB5BE3EF12&displaylang=en. SQL Server Books On Line is a very
useful repository of information about SQL Server.
Thank you for using the Microsoft newsgroups as your source for technical
information. In this particular case the complexity of your issue will
require in-depth troubleshooting and will not be best served by newsgroup
support. Please go to;
http://support.microsoft.com/common...=fh;en-us;cntac
tms
Select your region from the map and follow the instructions for contacting
our telephone support centers in your area.
Thank you.
Gary Whitley
This posting is provided "AS IS" with no warranties, and confers no rights.
that you mentioned in the last thread, I did find nc.exe
but no others. There were two versions, one in System32
from about a year ago but the other was in a subfolder
and was dated recently.
I'm embarrassed about my complete lack of SQL knowledge
(I shouldn't be because I never agreed to support it.)
but let me say this....
Please tell me that there IS supposed to be a "sql_admin"
local Windows account and that it IS supposed to be in
the local Administrators group.
quote:
>--Original Message--
>Charlie-
>glad this helped.
>another idea:
>do a find on all files created since your SQL install. i
>did this and found about 1,200 new files, however, the
>list was fairly easy to scan. the results were
revealing:
quote:
>1. some .exe files ended up in my windows\system32
>directory (previously i mentioned c:\winnt\... but this
>was not even where my system directory was so it was
very
quote:
>easy to spot this as a 'fake').
>i found:
>08/06/2000 01:51 AM 32,830 dbmsshrn.dll
>11/29/2003 03:38 PM 2,008 evntmanager.vxd
>11/24/2003 03:36 PM 126,976 fport.exe
>08/06/2000 01:50 AM 36,939 insrepim.exe
>07/07/2000 12:20 PM 81,920 mdt2fw95.dll
>11/29/2003 03:38 PM 548
msjet40enginev2.vxd
quote:
>11/30/2003 09:42 AM 1,161 msman32_1.dll
>11/30/2003 09:29 AM 937,984 msmantrt32.dll
>12/17/2002 05:24 PM 188,988 msrpjt40.dll
>08/06/2000 01:51 AM 274,489 ntwdblib.dll
>11/29/2003 08:29 AM 27,136 pskill.exe
>11/29/2003 08:30 AM 359,936 radv.exe
>11/29/2003 08:31 AM 130,048 rar.exe
>11/24/2003 03:36 PM 35,898 till.exe
>11/24/2003 03:36 PM 45,056 tk.exe
>of i which i think are placed by the "virus".
>2. a ton of data files in c: d: or e: (drives on my
>machine) hiding out in RECYCLER\_ many had .ra*
>extensions. they appeared to be names of popular DVD's
>and that my machine was hijacked to process or share
with
quote:Hi Charlie,
>other. these files took up GB's of space.
>/phil
>
>you
>looking
be[QUOTE]
vunerablity[QUOTE]
but[QUOTE]
>is
public[QUOTE]
my[QUOTE]
>running
>called
>these
on[QUOTE]
>but
was[QUOTE]
>ran
ports[QUOTE]
>MS
>.
>
Please check the logon account configured for SQL Server. I suspect the
individual that installed SQL Server created an account called SQL_Admin
and configured SQL Server and possibly SQL Agent to use this account.
You can check this in two places. One is SQL Enterprise Manager.
Highlight the server name, right click and select properties. Then, select
the security tab. That will display among other items the account used for
the SQL Server service.
To check the account for SQL Agent, open the Mangement folder for the
server, highlight SQL Agent. Right click and select properties. You will
be able to see the account used for the SQL Agent service in that area.
Of course, you can check the credentials in the Services area of Computer
Management as well. It is recommended that if you change the account
and/or password, that you do it through SQL Enterprise Manager.
Under the concept of least privilege, you will want to take that account
out of the Administrators group for the Windows server.
Thanks.
Gary
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have already checked in Services (sticking with what I
am very familiar with) and yes, those 2 SQL related
services are running under the sql_admin account.
From what you are saying, that account does not have to
be in the local Administrators group or in any other
group (besides Users, of course). Is that correct?
As for the person who set up the account and
installed/configured SQL - Got me! Therein lies the
problem. Many faculty members get tech resources without
support. I believe a student who is no longer around set
it up! Ahh, the joys of Academic IT.
quote:
>--Original Message--
>Hi Charlie,
>Please check the logon account configured for SQL
Server. I suspect the
quote:
>individual that installed SQL Server created an account
called SQL_Admin
quote:
>and configured SQL Server and possibly SQL Agent to use
this account.
quote:
>You can check this in two places. One is SQL Enterprise
Manager.
quote:
>Highlight the server name, right click and select
properties. Then, select
quote:
>the security tab. That will display among other items
the account used for
quote:
>the SQL Server service.
>To check the account for SQL Agent, open the Mangement
folder for the
quote:
>server, highlight SQL Agent. Right click and select
properties. You will
quote:
>be able to see the account used for the SQL Agent
service in that area.
quote:
>Of course, you can check the credentials in the Services
area of Computer
quote:
>Management as well. It is recommended that if you
change the account
quote:
>and/or password, that you do it through SQL Enterprise
Manager.
quote:
>Under the concept of least privilege, you will want to
take that account
quote:
>out of the Administrators group for the Windows server.
>Thanks.
>Gary
>This posting is provided "AS IS" with no warranties, and
confers no rights.
quote:|||The service account will automatically be granted rights if it's assigned
>.
>
with Enterprise Manager. The rights needed by the service account are
documented in this KB article:
http://support.microsoft.com/defaul...b;en-us;Q283811
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Charlie" <anonymous@.discussions.microsoft.com> wrote in message
news:024d01c3ba8d$4d274480$a101280a@.phx.gbl...[QUOTE]
> I have already checked in Services (sticking with what I
> am very familiar with) and yes, those 2 SQL related
> services are running under the sql_admin account.
> From what you are saying, that account does not have to
> be in the local Administrators group or in any other
> group (besides Users, of course). Is that correct?
> As for the person who set up the account and
> installed/configured SQL - Got me! Therein lies the
> problem. Many faculty members get tech resources without
> support. I believe a student who is no longer around set
> it up! Ahh, the joys of Academic IT.
>
> Server. I suspect the
> called SQL_Admin
> this account.
> Manager.
> properties. Then, select
> the account used for
> folder for the
> properties. You will
> service in that area.
> area of Computer
> change the account
> Manager.
> take that account
> confers no rights.|||Hi Charlie,
I reviewed the threads on this post today and realized a lot of ground has
been covered. I would like to summarize the issue if I may.
1. Your initial request concerned a question about multiple random TCP
ports on your server in communication with an external server via TCP port
1433. You were seeking help to determine if the box had been compromised.
2. Based on this information and additional information about files
located on the server, there is a chance the box has indeed been
compromised.
3. Your action at this time will depend on your objective. If you desire
to conduct a forensics examination on the server, you may want to consult
the information outlined at http://www.cert.org/nav/index_red.html in the
fixes section.
4. The following items specific to SQL Server configuration have been
discussed.
A. The 'SA' account must be secured with a very strong password.
B. The server should be shielded from the Internet by a firewall.
Specifically, TCP port 1433 should be closed for incoming packets from the
Internet.
C. Client/server communication with SQL Server is handled via incoming
packets to SQL over TCP port 1433 and outgoing packets to the clients on
one of the ports in the range 1024 to 5000. The specific port is
determined when the client connects.
D. The logon credentials for the SQL Server and SQL Agent accounts should
not be a member of the Administrators group on the server nor should they
be members of the Domain Admin group.
5. In the event you rebuild the server, SQL Server 2000 should be upgraded
to Service Pack 3a. A post SP3 security patch for SQL Server, MS03-031
should be applied. I also recommend applying MS03-033 which is a patch for
MDAC 2.7 SP1. When you upgrade SQL Server 2000 with SP3, MDAC 2.7 SP1 is
applied automatically.
6. In the event you rebuild the Windows server, please apply the
applicable Service Packs and security updates before placing the server on
the network and certainly before placing it on the Internet. The Windows
Update web site is very useful for cataloging the updates needed for a
specific build of the operating system. Also, the Microsoft Baseline
Security Analyzer is a useful tool to help you determine the patches and
configurations appropriate for your server. MBSA is available at
http://www.microsoft.com/downloads/...e63b-92e3-4f97-
80e7-8bc9ff836742&DisplayLang=en.
7. You may want to download the updated SQL Server Books On LIne. It has
been updated for SP3. This can be installed as a separate package. It is
availble at
http://www.microsoft.com/downloads/...71A6-BCF4-45A6-
A2E2-F6AB5BE3EF12&displaylang=en. SQL Server Books On Line is a very
useful repository of information about SQL Server.
Thank you for using the Microsoft newsgroups as your source for technical
information. In this particular case the complexity of your issue will
require in-depth troubleshooting and will not be best served by newsgroup
support. Please go to;
http://support.microsoft.com/common...=fh;en-us;cntac
tms
Select your region from the map and follow the instructions for contacting
our telephone support centers in your area.
Thank you.
Gary Whitley
This posting is provided "AS IS" with no warranties, and confers no rights.
Sunday, March 11, 2012
complicated Join - duplicate row problem
Hi
I had a similar problem awhile back and it was solved here. Now it has
gotten more complex. I have removed uneeded stuff here to keep this
simple.:
I am sure I know why I am getting the results I am, but don't know
how (or if) there is a way around it. If someone can help, I will be
mighty excited and impressed !
Here goes:
Given a table definition:
CREATE TABLE table1
(col1 varchar(20),
col2 varchar(20),
col3 integer,
CONSTRAINT PK_table1 PRIMARY KEY (col1,col2))
and then (assuming table2 exists)
ALTER TABLE table1
ADD CONSTRAINT FK_table1_table2
FOREIGN KEY (col1)
REFERENCES table2 (col1)
REQUIREMENT:
I would like to select some schema information about this table. Among
other things, I want the column name, data type, and Primary Key and
Foreign Key information. For the Primary Key and Foreign Key, all I
need to know is if one or both of these attributes applies to a
column.
Therefore, I would like my result records to look like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK | FK
col2 | varchar | PK |
col3 | integer | |
My problem is getting the PK and FK on the same row. My results are
currently like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK |
col1 | varchar | | FK
col2 | varchar | PK |
col3 | integer | |
HERE IS THE QUERY:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END,
FK_Col = case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
So, Col1 is duplicated because there are two (2) entries (PK & FK) in
key_column_usage for that column.
Is there some way to combine these two result rows together taking the
PK info from 1 and the FK from the other?
Thanks
JeffHi, Jeff
Did you read my response to your previous post ?
http://groups.google.com/group/micr...br />
a2bf3ce9
It's exactly what you are requesting now...
Razvan|||Sorry, I had missed your earlier reply because it didn't show up in my
news reader under the question. I see it now. My mistake.
This looks good and makes sense.
Thank you very very much !!
Jeff
On 12 Jan 2006 23:28:37 -0800, "Razvan Socol" <rsocol@.gmail.com>
wrote:
>Hi, Jeff
>Did you read my response to your previous post ?
>http://groups.google.com/group/micr...r />
9a2bf3ce9
>It's exactly what you are requesting now...
>Razvan|||On Fri, 13 Jan 2006 06:38:33 GMT, Jeff User wrote:
(snip)
>Therefore, I would like my result records to look like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK | FK
> col2 | varchar | PK |
> col3 | integer | |
>My problem is getting the PK and FK on the same row. My results are
>currently like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK |
> col1 | varchar | | FK
> col2 | varchar | PK |
> col3 | integer | |
(snip)
Hi Jeff,
Just a few simple changes to the query should suffice:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = MAX(case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END),
FK_Col = MAX(case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END)
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
GROUP BY cols.COLUMN_NAME, cols.DATA_TYPE, cols.ORDINAL_POSITION
Hugo Kornelis, SQL Server MVP
I had a similar problem awhile back and it was solved here. Now it has
gotten more complex. I have removed uneeded stuff here to keep this
simple.:
I am sure I know why I am getting the results I am, but don't know
how (or if) there is a way around it. If someone can help, I will be
mighty excited and impressed !
Here goes:
Given a table definition:
CREATE TABLE table1
(col1 varchar(20),
col2 varchar(20),
col3 integer,
CONSTRAINT PK_table1 PRIMARY KEY (col1,col2))
and then (assuming table2 exists)
ALTER TABLE table1
ADD CONSTRAINT FK_table1_table2
FOREIGN KEY (col1)
REFERENCES table2 (col1)
REQUIREMENT:
I would like to select some schema information about this table. Among
other things, I want the column name, data type, and Primary Key and
Foreign Key information. For the Primary Key and Foreign Key, all I
need to know is if one or both of these attributes applies to a
column.
Therefore, I would like my result records to look like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK | FK
col2 | varchar | PK |
col3 | integer | |
My problem is getting the PK and FK on the same row. My results are
currently like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK |
col1 | varchar | | FK
col2 | varchar | PK |
col3 | integer | |
HERE IS THE QUERY:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END,
FK_Col = case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
So, Col1 is duplicated because there are two (2) entries (PK & FK) in
key_column_usage for that column.
Is there some way to combine these two result rows together taking the
PK info from 1 and the FK from the other?
Thanks
JeffHi, Jeff
Did you read my response to your previous post ?
http://groups.google.com/group/micr...br />
a2bf3ce9
It's exactly what you are requesting now...
Razvan|||Sorry, I had missed your earlier reply because it didn't show up in my
news reader under the question. I see it now. My mistake.
This looks good and makes sense.
Thank you very very much !!
Jeff
On 12 Jan 2006 23:28:37 -0800, "Razvan Socol" <rsocol@.gmail.com>
wrote:
>Hi, Jeff
>Did you read my response to your previous post ?
>http://groups.google.com/group/micr...r />
9a2bf3ce9
>It's exactly what you are requesting now...
>Razvan|||On Fri, 13 Jan 2006 06:38:33 GMT, Jeff User wrote:
(snip)
>Therefore, I would like my result records to look like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK | FK
> col2 | varchar | PK |
> col3 | integer | |
>My problem is getting the PK and FK on the same row. My results are
>currently like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK |
> col1 | varchar | | FK
> col2 | varchar | PK |
> col3 | integer | |
(snip)
Hi Jeff,
Just a few simple changes to the query should suffice:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = MAX(case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END),
FK_Col = MAX(case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END)
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
GROUP BY cols.COLUMN_NAME, cols.DATA_TYPE, cols.ORDINAL_POSITION
Hugo Kornelis, SQL Server MVP
Subscribe to:
Posts (Atom)