Showing posts with label names. Show all posts
Showing posts with label names. Show all posts

Monday, March 26, 2012

Query with changing table names

We have a web tracking program that came with our firewall that writes it's
data to a MSDE database.
Unfortunately it writes each day's data to a different table. It names them
connection_events_2005_10_20 then connection_events_2005_10_21 etc... I need
to create a report by the w from all of these tables. Is there away to
query all of the tables that start with "connection_events_ " at the same
time? Is there a different way to deal with this?
I an trying to do this through an Access 2003 .ADP. I posted to that user
group and was refered here.
Thanks in advance for your suggestions.
SteveYou could set up a job to create a new view each day that would select from
the "ellusive" tables.
Tables names must be listed explicitly - wildcards are not applicable.
Basically, what you need is a view like in this example:
select <columns>
from <table_name>_2005-10-27
union all
select <columns>
from <table_name>_2005-10-28
union all
select <columns>
from <table_name>_2005-10-29
...
ML|||>> it writes each day's data to a different table. It names them connection
_events_2005_10_20 then connection_events_2005_10_21 etc... <<
You have just re-discovered 1950's magnetic tape processsing! You
almost mimicked the IBM naming conventions which gave tape labels the
markers 'yyddd' that we used when we did not have RDBMS!
Steve, you reallllllllllly need to stop programming and catch up with
RDBMS technology. You have missed the basics of RDBMS.
You keep the same data in the same table, period. A table is a set of
ALL --repeat ALL-- of those data elements. This is founjdations, not
rocket science. In RDBMS, you have a column (NOT a field like in the
1950's) that gives a duration, then you create VIEWs or derived tables
or subqueries.|||> You keep the same data in the same table, period.
well we have been using partitioning and UNION ALL views for quite a
while, not because we like doing it, but because it really boosts
performance. The performance price of following the advice to "keep the
same data in the same table" would be so huge - anyone actually doing
so might be fired on the spot...
Makes sense?|||I agree with your observations, however the program was written by the
firewall manufacturer so I have no control over it. I was surprised to see
how they setup this up but they aren't going to make changes for little 'ol
me. Their reports are lacking to say the least, so that's why I'm stuck with
this problem. Since I can't fix their program, are there any suggestions on
how to create a query that can pull the data from these tables together so
it's useful?
Thanks
Steve
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130449917.894438.155260@.z14g2000cwz.googlegroups.com...
> You have just re-discovered 1950's magnetic tape processsing! You
> almost mimicked the IBM naming conventions which gave tape labels the
> markers 'yyddd' that we used when we did not have RDBMS!
> Steve, you reallllllllllly need to stop programming and catch up with
> RDBMS technology. You have missed the basics of RDBMS.
> You keep the same data in the same table, period. A table is a set of
> ALL --repeat ALL-- of those data elements. This is founjdations, not
> rocket science. In RDBMS, you have a column (NOT a field like in the
> 1950's) that gives a duration, then you create VIEWs or derived tables
> or subqueries.
>|||I have hacked a script togethor that might do what you want. Let me
know what you think. I used a couple of previous posts on this group to
help. I am a bit of a newbie myself. I have tested the script as far as
the creation of the UNION SQL but not the overall procedure.
Celtic_Kiwi
/* Script begins */
CREATE PROCEDURE upLogViewCreate
AS
declare @.tablename varchar(32)
declare @.sql varchar(4000)
declare tnames_cursor cursor
for
select name
from sysobjects
where type = 'U'
and left(name,3) = '200'
open tnames_cursor
fetch next from tnames_cursor into @.tablename
set @.sql = ''
while (@.@.fetch_status <> -1)
begin
if (@.@.fetch_status <> -2)
begin
if @.sql = ''
begin
set @.sql = 'select * from ' + @.tablename
end
else
begin
set @.sql = @.sql + ' union select * from ' + @.tablename
end
end
fetch next from tnames_cursor into @.tablename
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
if ( (exists
(select *
from dbo.sysobjects
where id = object_id(N'[dbo].[vwLogView]')
and OBJECTPROPERTY(id, N'IsView') = 1
)
and
(@.sql<>'')
)
drop view [dbo].[qryNotesCandiac]
EXEC('CREATE VIEW dbo.vwLogView
AS ' + @.sql )|||>> we have been using partitioning and UNION ALL views for quite a while, no
t because we like doing it, but because it really boosts performance <<
No, we did it until SQL engines did a good job of implemenation that we
did not have to. Have you seen what DB2 does to detect a DW (fact and
dimension tables) ?
That is why we do not ever confuse implementation with logical models.
A partitioned table is not like multiple table at all. You are still
thinking that logical = physical because you are priogramming as if
this was the 1960's.|||You can do this with dynamic sql it goes like this;
steps;
1. select all tables from sysobjects table one after another
2. put that in to variable and use union query in dynamic sql
3. use exec(that query string) or sp_executesql
4. you have all rows in one view/derived table
Regards
R.D
--Knowledge gets doubled when shared
"Steve Roberts" wrote:

> We have a web tracking program that came with our firewall that writes it'
s
> data to a MSDE database.
> Unfortunately it writes each day's data to a different table. It names the
m
> connection_events_2005_10_20 then connection_events_2005_10_21 etc... I ne
ed
> to create a report by the w from all of these tables. Is there away to
> query all of the tables that start with "connection_events_ " at the same
> time? Is there a different way to deal with this?
> I an trying to do this through an Access 2003 .ADP. I posted to that user
> group and was refered here.
> Thanks in advance for your suggestions.
> Steve
>
>|||>> The performance price of following the advice to "keep the same data in
It depends on the specific schema/DBMS/physical implementation and the
performance price cannot be generalized. However the integrity issues
involved in doing so can be generalized. For some details, google for
"Principle of Orthogonal Design"
Anith|||Could you try creating views in advance of the firewall creating the tables?
then point all the views at the same table and have it log into a single
place?
IIRC, MSDE comes with sql agent and you could schedule a job to create the
views 7 days in advance, and destroy them once they're "old".
"Steve Roberts" <Stever@.Discussiongroups.com> wrote in message
news:OMwmRM12FHA.3296@.TK2MSFTNGP09.phx.gbl...
> I agree with your observations, however the program was written by the
> firewall manufacturer so I have no control over it. I was surprised to see
> how they setup this up but they aren't going to make changes for little
'ol
> me. Their reports are lacking to say the least, so that's why I'm stuck
with
> this problem. Since I can't fix their program, are there any suggestions
on
> how to create a query that can pull the data from these tables together so
> it's useful?
> Thanks
> Steve
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1130449917.894438.155260@.z14g2000cwz.googlegroups.com...
<<
>

Friday, March 23, 2012

Query using two column names in a table (to find rows near each other)

I have a table called Seats in my database...
CREATE TABLE [dbo].[Seats] (
[SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
[VehicleSerialNo] [int] NOT NULL ,
[RowNo] [smallint] NOT NULL ,
[ColumnNo] [smallint] NOT NULL ,
[SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
This table defines the seats that are available for a given VehicleSerialNo
(the typical scenario would be a motor coach or possibly an aircraft). A
seat has a RowNo (how far down the vehicle it is), a ColumnNo (it's position
left to right on the vehicle) and a SeatNo (the actual seat number the
customer is given - e.g. please sit in seat number 40 - these can be numeric
or like aircraft seats numbers 23D etc).
I have another table called Passengers which stores the VehicleSerialNo (the
actual motor coach) and the SeatSerialNo (the seat they are sitting in on
that motor coach). Let's now assume this table contains lots of entries
already specifying where the existing passengers will be sitting.
Example: I now want to add 6 people on this vehicle and automatically
allocate each person a seat. I am trying to figure out whether this
automatic seat selection can be achieved in SQL Server or whether this
should be done on the client side using VB. Ideally you would always like
to make sure all 6 people are sitting on the same part of the motor coach
(unless it is getting full). Would it be possible to construct a query that
would select seats that are near each other based on RowNo and ColumnNo
(excluding SeatSerialNo's that exists in the Passengers table - e.g. no
double booking of a seat)? So I want to return a query that returns the 6
seats the system thinks are best. Can such a query be performed comparing
these column names (RowNo and ColumnNo) finding seats that are near to each
other.
Many thanks,
ChrisC-W
Its hard to suggest without seeing sample data+ relationship+ expected
result.
Why you don't have a primary on the table?
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:O0cKXJanFHA.3312@.tk2msftngp13.phx.gbl...
>I have a table called Seats in my database...
>
> CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> This table defines the seats that are available for a given
> VehicleSerialNo (the typical scenario would be a motor coach or possibly
> an aircraft). A seat has a RowNo (how far down the vehicle it is), a
> ColumnNo (it's position left to right on the vehicle) and a SeatNo (the
> actual seat number the customer is given - e.g. please sit in seat number
> 40 - these can be numeric or like aircraft seats numbers 23D etc).
>
> I have another table called Passengers which stores the VehicleSerialNo
> (the actual motor coach) and the SeatSerialNo (the seat they are sitting
> in on that motor coach). Let's now assume this table contains lots of
> entries already specifying where the existing passengers will be sitting.
>
> Example: I now want to add 6 people on this vehicle and automatically
> allocate each person a seat. I am trying to figure out whether this
> automatic seat selection can be achieved in SQL Server or whether this
> should be done on the client side using VB. Ideally you would always like
> to make sure all 6 people are sitting on the same part of the motor coach
> (unless it is getting full). Would it be possible to construct a query
> that would select seats that are near each other based on RowNo and
> ColumnNo (excluding SeatSerialNo's that exists in the Passengers table -
> e.g. no double booking of a seat)? So I want to return a query that
> returns the 6 seats the system thinks are best. Can such a query be
> performed comparing these column names (RowNo and ColumnNo) finding seats
> that are near to each other.
>
> Many thanks,
> Chris
>
>|||Sorry, that's just the way I scripted the table. SeatSerialNo is the
primary key.
I will try and work on some sample data.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uwOTGPanFHA.320@.TK2MSFTNGP09.phx.gbl...
> C-W
> Its hard to suggest without seeing sample data+ relationship+ expected
> result.
> Why you don't have a primary on the table?
>|||On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:

>I have a table called Seats in my database...
>
>CREATE TABLE [dbo].[Seats] (
> [SeatSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [VehicleSerialNo] [int] NOT NULL ,
> [RowNo] [smallint] NOT NULL ,
> [ColumnNo] [smallint] NOT NULL ,
> [SeatNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>GO
Hi Chris,
You have two good candidate keys in the table without the extra identity
column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
ColumnNo). My advice would be to drop the SeatSerialNo column, declare
one of the composite candidate keys to be the PRIMARY KEY (probably the
one with SeatNo, but it depends on a lot of factors I don't know) and
define a UNIQUE constraint for the other one.

>I have another table called Passengers which stores the VehicleSerialNo (th
e
>actual motor coach) and the SeatSerialNo (the seat they are sitting in on
>that motor coach).
That's redundant. What if a passenger has VehicleSerialNo 1 and
SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
VehicleSerialNo 2?
If you keep SeatSerialNo in Seats and use it to refer to a seat in the
Passengers table, then remove VehicleSerialNo from the Passengers table
(the seat will always be in the same vehicle, regardless of who is
sitting on it). Or, if you drop SeatSerialNo from Seats, store the
combination of VehicleSerialNo and SeatNo in the Passengers table.
(snip)
>So I want to return a query that returns the 6
>seats the system thinks are best. Can such a query be performed comparing
>these column names (RowNo and ColumnNo) finding seats that are near to each
>other.
As Uri said. A repro script that others can run to recreate your test
data and the expected output would make it much easier to help you.
See www.aspfaq.com/5006 for more details and hints.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Once i've generate the script to reproduce this I will explain the table
structure further (and hopefully will all make sense). I tried to reproduce
a simple example before but probably caused more confusion. The Seats table
does not actually contain the VehicleSerialNo. Hopefully all will make
sense when I post my script.
Thanks,
Chris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:bg3kf15cvqe3r4k8hfo1jnr7uhpf52o3el@.
4ax.com...
> On Wed, 10 Aug 2005 12:54:59 +0100, C-W wrote:
>
> Hi Chris,
> You have two good candidate keys in the table without the extra identity
> column: (VehicleSerialNo, SeatNo) and (VehicleSerialNo, RowNo,
> ColumnNo). My advice would be to drop the SeatSerialNo column, declare
> one of the composite candidate keys to be the PRIMARY KEY (probably the
> one with SeatNo, but it depends on a lot of factors I don't know) and
> define a UNIQUE constraint for the other one.
>
> That's redundant. What if a passenger has VehicleSerialNo 1 and
> SeatSerialN0 17, but the row in Seats for SeatSerialNo says it's in
> VehicleSerialNo 2?
> If you keep SeatSerialNo in Seats and use it to refer to a seat in the
> Passengers table, then remove VehicleSerialNo from the Passengers table
> (the seat will always be in the same vehicle, regardless of who is
> sitting on it). Or, if you drop SeatSerialNo from Seats, store the
> combination of VehicleSerialNo and SeatNo in the Passengers table.
>
> (snip)
> As Uri said. A repro script that others can run to recreate your test
> data and the expected output would make it much easier to help you.
> See www.aspfaq.com/5006 for more details and hints.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Query using dynamic column names

Hi All,
I have a table DATA with following 20 columns.
COL1, COL2, COL3, COL4,..... COL20
Another table TEMPLATE has one column COLNAME. The table contains 5 rows with the data COL2, COL5, COL6, COL9, COL12 in the column COLNAME.
Can someone help me to build sql statement to select data from DATA table for only columns listed in the TEMPLATE table. I want to do this using only one select statement. (I know how to do this with stored procedure but thats not what I want)
Also it is possible that row in TEMPLATE table may change.
Hope to get some positive replies.
Reagrds,
HK
If all the columns are of compatible types then you can do it like this:
CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
NOT NULL UNIQUE)
INSERT INTO Template VALUES ('COL1',1)
INSERT INTO Template VALUES ('COL2',2)
INSERT INTO Template VALUES ('COL3',3)
SELECT MIN(CASE
WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
/* .. etc */
END),
MIN(CASE
WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
END),
MIN(CASE
WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
END),
...
FROM Data AS D
CROSS JOIN Template AS T
GROUP BY D.col1, D.col2, D.col3, ... other cols
Otherwise use IF statements or dynamic SQL. You could also consider building
the SELECT statement client-side or retrieve all the columns but display
only the required ones to the user.
David Portas
SQL Server MVP
|||Thanks for your help David. But this will not work for me. The example I gave was to explain my problem. But in reality, I will not know all column names in the DATA table, only TEMPLATE table is the guide which has list of fields to select from DATA tabl
e.
Regards,
HK
"David Portas" wrote:

> If all the columns are of compatible types then you can do it like this:
> CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
> NOT NULL UNIQUE)
> INSERT INTO Template VALUES ('COL1',1)
> INSERT INTO Template VALUES ('COL2',2)
> INSERT INTO Template VALUES ('COL3',3)
> SELECT MIN(CASE
> WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
> /* .. etc */
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
> END),
> ...
> FROM Data AS D
> CROSS JOIN Template AS T
> GROUP BY D.col1, D.col2, D.col3, ... other cols
> Otherwise use IF statements or dynamic SQL. You could also consider building
> the SELECT statement client-side or retrieve all the columns but display
> only the required ones to the user.
> --
> David Portas
> SQL Server MVP
> --
>
>

Query using dynamic column names

Hi All,
I have a table DATA with following 20 columns.
COL1, COL2, COL3, COL4,..... COL20
Another table TEMPLATE has one column COLNAME. The table contains 5 rows wi
th the data COL2, COL5, COL6, COL9, COL12 in the column COLNAME.
Can someone help me to build sql statement to select data from DATA table fo
r only columns listed in the TEMPLATE table. I want to do this using only on
e select statement. (I know how to do this with stored procedure but thats n
ot what I want)
Also it is possible that row in TEMPLATE table may change.
Hope to get some positive replies.
Reagrds,
HKIf all the columns are of compatible types then you can do it like this:
CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
NOT NULL UNIQUE)
INSERT INTO Template VALUES ('COL1',1)
INSERT INTO Template VALUES ('COL2',2)
INSERT INTO Template VALUES ('COL3',3)
SELECT MIN(CASE
WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
/* .. etc */
END),
MIN(CASE
WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
END),
MIN(CASE
WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
END),
..
FROM Data AS D
CROSS JOIN Template AS T
GROUP BY D.col1, D.col2, D.col3, ... other cols
Otherwise use IF statements or dynamic SQL. You could also consider building
the SELECT statement client-side or retrieve all the columns but display
only the required ones to the user.
David Portas
SQL Server MVP
--|||Thanks for your help David. But this will not work for me. The example I gav
e was to explain my problem. But in reality, I will not know all column name
s in the DATA table, only TEMPLATE table is the guide which has list of fiel
ds to select from DATA tabl
e.
Regards,
HK
"David Portas" wrote:

> If all the columns are of compatible types then you can do it like this:
> CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGE
R
> NOT NULL UNIQUE)
> INSERT INTO Template VALUES ('COL1',1)
> INSERT INTO Template VALUES ('COL2',2)
> INSERT INTO Template VALUES ('COL3',3)
> SELECT MIN(CASE
> WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
> /* .. etc */
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
> END),
> ...
> FROM Data AS D
> CROSS JOIN Template AS T
> GROUP BY D.col1, D.col2, D.col3, ... other cols
> Otherwise use IF statements or dynamic SQL. You could also consider buildi
ng
> the SELECT statement client-side or retrieve all the columns but display
> only the required ones to the user.
> --
> David Portas
> SQL Server MVP
> --
>
>

Query using dynamic column names

Hi All,
I have a table DATA with following 20 columns.
COL1, COL2, COL3, COL4,..... COL20
Another table TEMPLATE has one column COLNAME. The table contains 5 rows with the data COL2, COL5, COL6, COL9, COL12 in the column COLNAME.
Can someone help me to build sql statement to select data from DATA table for only columns listed in the TEMPLATE table. I want to do this using only one select statement. (I know how to do this with stored procedure but thats not what I want)
Also it is possible that row in TEMPLATE table may change.
Hope to get some positive replies.
Reagrds,
HKIf all the columns are of compatible types then you can do it like this:
CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
NOT NULL UNIQUE)
INSERT INTO Template VALUES ('COL1',1)
INSERT INTO Template VALUES ('COL2',2)
INSERT INTO Template VALUES ('COL3',3)
SELECT MIN(CASE
WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
/* .. etc */
END),
MIN(CASE
WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
END),
MIN(CASE
WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
END),
...
FROM Data AS D
CROSS JOIN Template AS T
GROUP BY D.col1, D.col2, D.col3, ... other cols
Otherwise use IF statements or dynamic SQL. You could also consider building
the SELECT statement client-side or retrieve all the columns but display
only the required ones to the user.
--
David Portas
SQL Server MVP
--|||Thanks for your help David. But this will not work for me. The example I gave was to explain my problem. But in reality, I will not know all column names in the DATA table, only TEMPLATE table is the guide which has list of fields to select from DATA table.
Regards,
HK
"David Portas" wrote:
> If all the columns are of compatible types then you can do it like this:
> CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
> NOT NULL UNIQUE)
> INSERT INTO Template VALUES ('COL1',1)
> INSERT INTO Template VALUES ('COL2',2)
> INSERT INTO Template VALUES ('COL3',3)
> SELECT MIN(CASE
> WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
> /* .. etc */
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
> END),
> ...
> FROM Data AS D
> CROSS JOIN Template AS T
> GROUP BY D.col1, D.col2, D.col3, ... other cols
> Otherwise use IF statements or dynamic SQL. You could also consider building
> the SELECT statement client-side or retrieve all the columns but display
> only the required ones to the user.
> --
> David Portas
> SQL Server MVP
> --
>
>

Tuesday, March 20, 2012

Query to return database file names and locations

Does anyone know a query that will return all the databases (SQL 2005) on a server with their file names and locations?

Thanks

You could always just use the standard sp_helpdb.

EXEC sp_helpdb <dbname>

|||

In SQL SERVER 2005, catalog view sys.master_files exposes this information.

So,

select db_name(database_id) database_name, * from sys.master_files

will return all database file names and locations (except resource database)

query to list field data types

Can anyone tell me if there's a query command that will
return information about a table's properties, such as
field names, field data types, field lengths, index names,
etc?
For example, I'm looking for output that would show the
following, if we queried the Customers table in the MS
Northwind SQL DB:
CustomerID, nchar, 5, nulls=no
CompanyName, nvarchar, 40, nulls=no
ContactName, nvarchar, 30, nulls=yes
etc...
Thanks!!!
David
On Mon, 19 Apr 2004 10:04:25 -0700, David Hepburn III wrote:

>Can anyone tell me if there's a query command that will
>return information about a table's properties, such as
>field names, field data types, field lengths, index names,
>etc?
>For example, I'm looking for output that would show the
>following, if we queried the Customers table in the MS
>Northwind SQL DB:
>CustomerID, nchar, 5, nulls=no
>CompanyName, nvarchar, 40, nulls=no
>ContactName, nvarchar, 30, nulls=yes
>etc...
>Thanks!!!
>David
Hi David,
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers'
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi david,
you can make use of any of the following system stored procedures.
sp_columns <table_name> --to find column definition
sp_helpindex <table_name> --to find indexes
sp_help <table_name> complete table definition including indexes,pks/fks
etc.
Vishal
vgparkar@.yahoo.co.in

Monday, March 12, 2012

Query to get the names of items with different levels

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

Now I want to show this data as

Root -> Trunk -> Branch -> Leaf

How to write the query for getting the Names for different levels for corresponding ParentID...select case when level is 1 then Name end as Root,
case when level is 2 then Name end as Trunk,
case when level is 3 then Name end as Branch,
case when level is 4 then Name end as Leaf,
case when level is 5 then Name end as Stem

Quote:

Originally Posted by sree078

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

Now I want to show this data as

Root -> Trunk -> Branch -> Leaf

How to write the query for getting the Names for different levels for corresponding ParentID...

Query to get name of views which reference a give column from a ta

Hi
Can somebody give me a query to find the name of all the views which referen
ce
a give column name of a table
ex: i want to find names of all views which have the column account_type
from the table accounts.
Thanks
RodgerRodger,
check view INFORMATION_SCHEMA.COLUMNS
Example:
use northwind
go
declare @.cn sysname
set @.cn = N'OrderID'
select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = @.cn and
objectproperty(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'),
'IsView') = 1
go
AMB
"Rodger" wrote:

> Hi
> Can somebody give me a query to find the name of all the views which refer
ence
> a give column name of a table
> ex: i want to find names of all views which have the column account_type
> from the table accounts.
> Thanks
> Rodger

Query to get name of views which reference a give column from

I have 3 to 4 tables with the same column names, so it displays views for al
l
the tables ,
any additions '
Thanks a Lot
"Alejandro Mesa" wrote:
> Rodger,
> check view INFORMATION_SCHEMA.COLUMNS
> Example:
> use northwind
> go
> declare @.cn sysname
> set @.cn = N'OrderID'
> select TABLE_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where COLUMN_NAME = @.cn and
> objectproperty(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'),
> 'IsView') = 1
> go
>
> AMB
> "Rodger" wrote:
>use information_Schema.view_column_usage
"Rodger" wrote:
> I have 3 to 4 tables with the same column names, so it displays views for
all
> the tables ,
> any additions '
> Thanks a Lot
> "Alejandro Mesa" wrote:
>|||It worked...great... never tried these wonderful views.
Thanks
rodger
"Omnibuzz" wrote:
> use information_Schema.view_column_usage
> "Rodger" wrote:
>|||Check out all the view in INFORMATION_SCHEMA. if you are interested. gives a
lot of metadata. And if you have no further questions can you close this
thred.
Thanks
Omnibuzz
"Rodger" wrote:
> It worked...great... never tried these wonderful views.
> Thanks
> rodger
> "Omnibuzz" wrote:
>

Query to get a list of field/column names with the tablm name.

Thanks for reading my question.

I need some help on a query which will provide me with the following information.

TableName ColumName

Table1 Column1

Table1 Column2

Table1 Column3

Table2 Column1

Table2 Column2

Table2 Column3

Table2 Column1

Table3 Column1

Table3 Column2

Thanks.

I got it...

SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS

ColumnName, dbo.systypes.name AS DataType, dbo.syscolumns.length,

dbo.syscolumns.xprec, dbo.syscolumns.xscale,

dbo.syscolumns.colid AS ColumnSort, dbo.sysindexkeys.colid

FROM dbo.sysobjects INNER JOIN

dbo.syscolumns ON dbo.sysobjects.id =

dbo.syscolumns.id INNER JOIN

dbo.systypes ON dbo.syscolumns.xusertype =

dbo.systypes.xusertype LEFT OUTER JOIN

dbo.sysindexkeys ON dbo.syscolumns.colid =

dbo.sysindexkeys.colid AND dbo.syscolumns.id = dbo.sysindexkeys.id

WHERE (dbo.sysobjects.xtype = 'U')

|||

Thanks for following up your own post. It prevents others from wasting their time trying to help you after you have solved the problem, and it helps others when you share your solution.

|||

use information_schema.columns

Code Snippet

select table_name,column_name from information_schema.columns

order by table_name,ordinal_position

|||

select Table_catalog as Databasename, table_name, column_name from INFORMATION_SCHEMA.COLUMNS

|||

DON'T write your query against the SYSTEM tables, the schema might change without any notice. Always trust with information_schema.xxxx views.

I do like to play with system tables, but when it come to solid solutions, we have to stick with few coditions. Smile|||

Sorry for that unnecessary answer from me. Arnie/Mani and Team are really quick to answer any question. When i open this thread nobody was answered. But when i completed typing there were two reply... Really quick buddies...

g8 keep it up

Madhu

|||

Madhu,

Sometimes that happens this time of day. For slower responses, and a longer 'window of opportunity, try about four hours later in the day.

Friday, March 9, 2012

Query to find databases in full mode

I have a stored proc that runs through my databases doing transaction logs. Currently it uses database names but this keeps being changed as a database will be added without me knowing. Next thing the transaction log job is goosed.

I really need something along the lines of select db from sys? where mode = 'Full'

Any help appreciated.

MPM

select * from sys.databases

where recovery_model_desc = 'FULL'

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
>
>

Monday, February 20, 2012

query synonyms

I'm using a full text index to search organization names. My language word
breaker is French (France).
My situation is that if I search for example for a organization with the word
"meilleur" in the name ("meilleur" means "greatest" in english), the answer
that the search will give me (using freetext or formsof(inflectional))is all
the organizations with "meilleur", "bon" and "bonne" in there name. But the
thing is that my client don't want the organizations with "bon" and "bonne"
(who means "good" in english) for there answer.
I made a little search and the answer that I found is that these are synonyms
and they should be listed in my tsfra.xml file. This file looks like this :
- <XML ID="Tahoe Thesaurus">
- <!--
Commented out
<thesaurus xmlns="x-schema:tsSchema.xml">
<expansion>
<sub weight="0.8">Internet Explorer</sub>
<sub weight="0.2">IE</sub>
<sub weight="0.9">IE5</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub weight="1.0">Windows 2000</sub>
</replacement>
<expansion>
<sub weight="0.5">run**</sub>
<sub weight="0.5">jog**</sub>
</expansion>
</thesaurus>
-->
</XML>
there's nothing that indicate that meilleur is synonyms of good.
Know I wonder is there another files somewhere who can have these synonyms?
Could it be in the Microsoft search engine?
Thanks in advance,
Nik
P.S. Sorry for my english
Message posted via http://www.droptable.com
This is hard coded in the stemmer. Unfortunately there is nothing you can do
other than to write your own stemmer.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"nick via droptable.com" <forum@.droptable.com> wrote in message
news:52A31264BED2E@.droptable.com...
> I'm using a full text index to search organization names. My language
> word
> breaker is French (France).
> My situation is that if I search for example for a organization with the
> word
> "meilleur" in the name ("meilleur" means "greatest" in english), the
> answer
> that the search will give me (using freetext or formsof(inflectional))is
> all
> the organizations with "meilleur", "bon" and "bonne" in there name. But
> the
> thing is that my client don't want the organizations with "bon" and
> "bonne"
> (who means "good" in english) for there answer.
> I made a little search and the answer that I found is that these are
> synonyms
> and they should be listed in my tsfra.xml file. This file looks like this
> :
> - <XML ID="Tahoe Thesaurus">
> - <!--
> Commented out
> <thesaurus xmlns="x-schema:tsSchema.xml">
> <expansion>
> <sub weight="0.8">Internet Explorer</sub>
> <sub weight="0.2">IE</sub>
> <sub weight="0.9">IE5</sub>
> </expansion>
> <replacement>
> <pat>NT5</pat>
> <pat>W2K</pat>
> <sub weight="1.0">Windows 2000</sub>
> </replacement>
> <expansion>
> <sub weight="0.5">run**</sub>
> <sub weight="0.5">jog**</sub>
> </expansion>
> </thesaurus>
>
> -->
> </XML>
>
> there's nothing that indicate that meilleur is synonyms of good.
> Know I wonder is there another files somewhere who can have these
> synonyms?
> Could it be in the Microsoft search engine?
> Thanks in advance,
> Nik
> P.S. Sorry for my english
>
> --
> Message posted via http://www.droptable.com