Query for Table structure

Hi,
I'm looking for some code that will pull back a list all tables in a database that meet a certain requirement - in this case all tables that have an identity column.
Can anyone point me in the right direction?
Cheers
Gregselect object_name(id),* from syscolumns
where autoval is not null

Query Empty node

Hello,

I have an XML file which contains different shops, where a certain node (<openinhours>) is empty in some occasions.

The node displays as <openinghours /> in my xml file in that case.

Now when I try to get the openinghour value of all the shops, it shows an error that I cannot atomize.

Is there any way to check if the node contains text by using the value or exist?

Thanks in advance!

I will try to clarify myself.

In my db, I have every shop in a different row, with columns: ShopID, ShopName, ShopXML. ShopXML contains the rest of the info of te shop, and is xml-field.

In that xml, i have something like this:

<shopinfo><openinghours>9a.m. - 18p.m.</openinghours> ...more xml ...</shopinfo>

However, in some cases this just is:

<shopinfo><openinghours /> ...more xml... </shopinfo>

I want to get all the openinghours where available from my xml (and I use .value),

but using

ShopXML.value('(/shopinfo/openinghours)[1]','varchar(max)') doesn't work because of the empty nodes.

How can I solve this?

Thank you!

|||

Thanks for providing more information. I'll have to ask some more questions before I can help you.

In the previous post you said that you got an error saying that you cannot atomize. Which is the exact error message that you get? I would assume that it is a static error, which means that the ShopXML is typed with an XML schema collection. Is this true? If it is, what's the type of openinghours element?

Thanks,

Adrian

|||

Yes I have a schema collection. The type is string.

When I disable the schemas it works fine. But if I choose not to use the schema's, will it affect the speed of querying data in my XML field?

|||

It's better to use the schema collection if you can.

I still don't understand what exactly doesn't work when you have a schema collection. Do you get an error? If yes, what error do you get and when? Or is it that you don't want to have empty openinghours in your result?

Regards,

Adrian

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14),
just records where any value occurs more than once. Any suggestions?
Many thanks
Homer
Homer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer
|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14)
,
just records where any value occurs more than once. Any suggestions?
Many thanks
HomerHomer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=1
4),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer

Query duplicates

I would like to create a query that will return all of the rows in a table
where the data in a certain column occurs more than once. I don't want to
return records with specific values(e.g. SELECT * from mytable WHERE age=14),
just records where any value occurs more than once. Any suggestions?
Many thanks
HomerHomer
Look at Itzik Ben-Gan's example
--Modify it for your needs
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:CABBD9CA-DD66-4974-8B37-96216FF153C9@.microsoft.com...
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE
age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer|||Homer,
Here's my test script for your issue:
create table Homerdupes (
i int not null primary key identity
, j int not null
, v varchar(50))
insert Homerdupes (j,v) values (100,'Hello')
insert Homerdupes (j,v) values (200,'Goodbye')
insert Homerdupes (j,v) values (200,'Goodbye - DUPE!!')
-- show me the data
select * from Homerdupes
-- show me the dupes
select a.* from Homerdupes a join
(select j,count(*) as counter from Homerdupes
group by j
having count(*) > 1) as d
on a.j = d.j
drop table Homerdupes
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Homer wrote:
> I would like to create a query that will return all of the rows in a table
> where the data in a certain column occurs more than once. I don't want to
> return records with specific values(e.g. SELECT * from mytable WHERE age=14),
> just records where any value occurs more than once. Any suggestions?
> Many thanks
> Homer