how to find which object belongs to which File group

Hi
We have a DB in which we have created two file groups. Both are currently
Primary file group. what is the best way to find out which objects are in
which file group?
Thanks,This doesn't make sense. "Both are currently Primary file group." Only one
filegroup can be the primary filegroup.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||I can mail you screen shot of EM where two files are listed as primary file
groups.
Moh
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only one
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> > Hi
> > We have a DB in which we have created two file groups. Both are currently
> > Primary file group. what is the best way to find out which objects are in
> > which file group?
> >
> > Thanks,
> >
> >
>
>|||That's not what you said. You said "we have created two file groups". Now,
you're saying you created two files. Which is it?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:A0603CAB-EDF1-46BF-B449-D989432DF7DF@.microsoft.com...
>I can mail you screen shot of EM where two files are listed as primary file
> groups.
> Moh
> "Tom Moreau" wrote:
>> This doesn't make sense. "Both are currently Primary file group." Only
>> one
>> filegroup can be the primary filegroup.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Moh" <mabbas@.Pier1.com> wrote in message
>> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>> > Hi
>> > We have a DB in which we have created two file groups. Both are
>> > currently
>> > Primary file group. what is the best way to find out which objects are
>> > in
>> > which file group?
>> >
>> > Thanks,
>> >
>> >
>>|||By the way for your knowledge, you can have more than one files belongs to a
File group(Primary FG or any other). you can not have one file belongs to
more than one FG.
Does that mamke sense?
thx,
Moh
--
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
"Tom Moreau" wrote:
> This doesn't make sense. "Both are currently Primary file group." Only one
> filegroup can be the primary filegroup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> > Hi
> > We have a DB in which we have created two file groups. Both are currently
> > Primary file group. what is the best way to find out which objects are in
> > which file group?
> >
> > Thanks,
> >
> >
>
>|||I'm already aware of that. So, what exactly is the problem?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Moh" <mabbas@.Pier1.com> wrote in message
news:D6BCED88-53DB-4DA8-BC41-00E355065EF0@.microsoft.com...
> By the way for your knowledge, you can have more than one files belongs to
> a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
>> This doesn't make sense. "Both are currently Primary file group." Only
>> one
>> filegroup can be the primary filegroup.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Moh" <mabbas@.Pier1.com> wrote in message
>> news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
>> > Hi
>> > We have a DB in which we have created two file groups. Both are
>> > currently
>> > Primary file group. what is the best way to find out which objects are
>> > in
>> > which file group?
>> >
>> > Thanks,
>> >
>> >
>>|||a filegroup is composed of one or more files
Objects are placed on filegroups and not files
If you wanted to know on which filegroup an object is placed, use sp_help
@.objectname
or the undocumented sp_objectfilegroup @.objid
If you wanted to know the list of all objects that belong to a filegroup
you can query sysindexes and group by the result by groupid
Med Bouchenafa
"Moh" <mabbas@.Pier1.com> a écrit dans le message de news:
B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> Hi
> We have a DB in which we have created two file groups. Both are currently
> Primary file group. what is the best way to find out which objects are in
> which file group?
> Thanks,
>|||Dear M Abbas,
From your 3 posts in this Response, it is clear that you need to improve in
following areas:
1. English Language Skills: There are both grammatical as well as spelling
mistakes in your posts. Example of Grammatical Mistake: â'Both are currently
Primary file groupâ'. Example of Spelling Mistake: â'â?¦.mamke senseâ'.
2. Behavioural Skills: Tom Moreau didnâ't make any wrong statements. It is
you who made wrong statement in the 1st post by saying â'â?¦we have created two
file groups. Both are currently Primary file groupâ'. Still, you have
expressed anger in your 3rd post.
3. SQL Server Skills.
Remember, that the first person to benefit from these improvements will be
you. So choice is yours.
"Moh" wrote:
> By the way for your knowledge, you can have more than one files belongs to a
> File group(Primary FG or any other). you can not have one file belongs to
> more than one FG.
> Does that mamke sense?
> thx,
> Moh
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Tom Moreau" wrote:
> > This doesn't make sense. "Both are currently Primary file group." Only one
> > filegroup can be the primary filegroup.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada tom@.cips.ca
> > www.pinpub.com
> >
> > "Moh" <mabbas@.Pier1.com> wrote in message
> > news:B93809BB-695B-4EED-9CE0-B3154B7D8301@.microsoft.com...
> > > Hi
> > > We have a DB in which we have created two file groups. Both are currently
> > > Primary file group. what is the best way to find out which objects are in
> > > which file group?
> > >
> > > Thanks,

How to find the duplicate value

I want to set a filed to primary key.
But there duplicate value in it.
How can I find all rows with the duplicate value of that field?
ad
Itzik Ben-Gan written a greate examples about that
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
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
> I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>
|||Hi
If the whole row is duplicated then you can create a temporary table with
the same structure that is populated with
INSERT INTO #tmp SELECT DISTINCT * FROM MyTable
you can then
TRUNCATE MyTable
and re-insert the value back
INSERT INTO MyTable SELECT * FROM #tmp
If this is not the case, then you will need to differentiate the records
somehow and then choose one to keep e.g. If there say a datetime column
called date_created and you wish to keep the earliest and you primary key is
a column(s) called PK then (assuming date_created is unique for each pk)
DELETE FROM MyTable
FROM MyTable t
WHERE t.date_created > ( SELECT MIN(date_created) FROM MyTable M where m.pk
= t.pk )
John
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>
|||On Mon, 28 Mar 2005 16:19:13 +0800, ad wrote:

>I want to set a filed to primary key.
>But there duplicate value in it.
>How can I find all rows with the duplicate value of that field?
>
Hi ad,
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo left out a little part on his query... on the having clause
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*) > 1
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>
|||On Mon, 28 Mar 2005 08:26:42 -0500, Wayne Snyder wrote:

>Hugo left out a little part on his query... on the having clause
Ouch! Thanks for catching that, Wayne!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to find the duplicate value

I want to set a filed to primary key.
But there duplicate value in it.
How can I find all rows with the duplicate value of that field?ad
Itzik Ben-Gan written a greate examples about that
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
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
> I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||Hi
If the whole row is duplicated then you can create a temporary table with
the same structure that is populated with
INSERT INTO #tmp SELECT DISTINCT * FROM MyTable
you can then
TRUNCATE MyTable
and re-insert the value back
INSERT INTO MyTable SELECT * FROM #tmp
If this is not the case, then you will need to differentiate the records
somehow and then choose one to keep e.g. If there say a datetime column
called date_created and you wish to keep the earliest and you primary key is
a column(s) called PK then (assuming date_created is unique for each pk)
DELETE FROM MyTable
FROM MyTable t
WHERE t.date_created > ( SELECT MIN(date_created) FROM MyTable M where m.pk
= t.pk )
John
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||On Mon, 28 Mar 2005 16:19:13 +0800, ad wrote:

>I want to set a filed to primary key.
>But there duplicate value in it.
>How can I find all rows with the duplicate value of that field?
>
Hi ad,
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo left out a little part on his query... on the having clause
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*) > 1
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||On Mon, 28 Mar 2005 08:26:42 -0500, Wayne Snyder wrote:

>Hugo left out a little part on his query... on the having clause
Ouch! Thanks for catching that, Wayne!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to find the duplicate value

I want to set a filed to primary key.
But there duplicate value in it.
How can I find all rows with the duplicate value of that field?ad
Itzik Ben-Gan written a greate examples about that
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
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
> I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||Hi
If the whole row is duplicated then you can create a temporary table with
the same structure that is populated with
INSERT INTO #tmp SELECT DISTINCT * FROM MyTable
you can then
TRUNCATE MyTable
and re-insert the value back
INSERT INTO MyTable SELECT * FROM #tmp
If this is not the case, then you will need to differentiate the records
somehow and then choose one to keep e.g. If there say a datetime column
called date_created and you wish to keep the earliest and you primary key is
a column(s) called PK then (assuming date_created is unique for each pk)
DELETE FROM MyTable
FROM MyTable t
WHERE t.date_created > ( SELECT MIN(date_created) FROM MyTable M where m.pk
= t.pk )
John
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||On Mon, 28 Mar 2005 16:19:13 +0800, ad wrote:
>I want to set a filed to primary key.
>But there duplicate value in it.
>How can I find all rows with the duplicate value of that field?
>
Hi ad,
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo left out a little part on his query... on the having clause
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*) > 1
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||On Mon, 28 Mar 2005 08:26:42 -0500, Wayne Snyder wrote:
>Hugo left out a little part on his query... on the having clause
Ouch! Thanks for catching that, Wayne!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to find out tables which cant be replicated

Is there any query to find out all the tables without a Primary key or without a Unique index ?select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME not in(
select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE in('PRIMARY KEY', 'UNIQUE')
)|||I am assuming for Transactional Replication, minimum requirement is a unique index !

Thanks for above query|||BOL:

Microsoft SQL Server 2000 automatically creates unique indexes to enforce the uniqueness requirements of PRIMARY KEY and UNIQUE constraints.

My query returns list of tables without PRIMARY KEY or UNIQUE constraints. Try it on your database.

Former Kentuckian.

How to find missing records from tables involving composite primary keys

Table 1

Code	Quarter
500002	26
500002	27
500002	28
500002	28.5
500002	29

Table 2

Code	Qtr
500002	26
500002	27

I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS

Can anybody help me with how to compare the two tables to find the records not present in Table 2

That is i need this result

Code	Quarter
500002	28
500002	28.5
500002	29

I have come up with this solution

select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)

i need to know if there is some other way of doing the same

Thanks in Advance

Jacx

You can use the following query too...

Select
A.Code,
A.Quarter
From
[Table 1] A
Left Outer Join [Table 2] B on A.Code = B.Code And A.Quarter = B.Quarter
Where
B.Code is NULL

|||

Using NOT EXISTS is the fastest way to solve your problem. Use query below:

select t1.scrip_cd, t1.Qtr

from Table1 as t1

where not exists(

select * from Table2 as t2

where t2.scrip_cd = t1.scrip_cd and t2.Qtr = t1.Qtr

)

How to find a record by number

What would be the best way in SQL to query a table to return a single record by number?
Let's say I want record number 49 and my primary key is a text field so a numeric key is out of the question.I think I figured it out unless anyone has a better way.

SELECT TOP 1 * FROM (SELECT TOP 49 * FROM [Table] ORDER BY [Key] ASC) AS Whatever ORDER BY [Key] DESC|||That looks like a bad idea. If you pass any number bigger than the max number of records in the table, you always get the last record.

any ideas?|||which row did you want, the 49th? by which column?

and yes, if there are fewer than 49 rows, it would seem appropriate (at least, it seems so to me) to choose the last one

what database are you using? this forum is for non-specific (i.e. standard) sql

you may want to consider the Access or SQL Server forums, if TOP works in your database