Finding Duplicate Names in a Simple Table

This is probably so easy it will be stupid but I cannot get anything to
work.
I need to find, from one table, all duplicate names. I pull the entire list
like this:
SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
FROM Attendants
What I need from this is a list where the names of the clients appear more
than once. I know there has to be an easy way to do this. Any quick help'SELECT First_Name,last_Name, count(*)
FROM Attendants
group by first_name, last_name
having count(*) > 1
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||SELECT ID, RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name) , Date_of_Birth
FROM Attendants AS a
WHERE EXISTS (SELECT RTRIM(b.First_Name) + ' ' + RTRIM(b.Last_Name)
FROM Attendants AS b
WHERE a.First_Name = b.First_Name and
a.Last_Name = b.Last_Name
GROUP BY RTRIM(b.First_Name) + ' ' +
RTRIM(b.Last_Name)
HAVING COUNT(*) > 1 )
ORDER BY RTRIM(a.First_Name) + ' ' + RTRIM(a.Last_Name)
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
> This is probably so easy it will be stupid but I cannot get anything to
> work.
> I need to find, from one table, all duplicate names. I pull the entire
> list like this:
> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
> FROM Attendants
> What I need from this is a list where the names of the clients appear more
> than once. I know there has to be an easy way to do this. Any quick help'|||Thanks Geoff
Works GREAT!
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:u0lwu%237%23HHA.1164@.TK2MSFTNGP02.phx.gbl...
> SELECT First_Name,last_Name, count(*)
> FROM Attendants
> group by first_name, last_name
> having count(*) > 1
>
> --
> Geoff Chovaz
> MCTS: SQL Server 2005
> MCITP: Database Administrator
> MCITP: Database Developer
>
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:43D97106-6DC7-495F-868A-109B4738A688@.microsoft.com...
>> This is probably so easy it will be stupid but I cannot get anything to
>> work.
>> I need to find, from one table, all duplicate names. I pull the entire
>> list like this:
>> SELECT ID, First_Name + ' ' + Last_Name, Date_of_Birth
>> FROM Attendants
>> What I need from this is a list where the names of the clients appear
>> more than once. I know there has to be an easy way to do this. Any quick
>> help'
>

Finding Duplicate Foreign Keys

Hi

i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column

select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc

but i am not able to get the fks created more than once on same column refering to same pk

Thanks in AdvanceIs this helpful?

Field1 has the following values;
1, 2, 3, 3, 4, 5, 6, 6, 6, 7, 8, 9
duplicates highlighted in bold

The following SQL will pick out duplicates only

SELECT Field1
FROM MyTable
GROUP BY Field1
HAVING Count(*) > 1

Field1
--
3
6

:cool:|||I am not sure what is the exact problem, but I think 'Information_Schema' might help. It contains number of views that can be used to retrive meta data.

E.g.
Select * from information_Schema.TABLE_CONSTRAINTS|||here's one that I use in SqlSpec, it may be more than you need though:

select
quotename(su2.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so2.name) collate Latin1_General_CI_AS as parent
,quotename(su1.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so1.name) collate Latin1_General_CI_AS as name
,so1.name as shortname ,sc2.name as colName
,'foreign key from ' + quotename(su2.name) + N'.' + quotename(so2.name) + ' to ' + quotename(su3.name) + N'.' + quotename(so3.name) as description
,quotename(su3.name) collate Latin1_General_CI_AS + N'.' collate Latin1_General_CI_AS + quotename(so3.name) collate Latin1_General_CI_AS as refTable
,sc3.name as refColumn
,ObjectProperty(sf.constid, 'CnstIsDisabled') as isDisabled
,ObjectProperty(sf.constid, 'CnstIsNotRepl') as isNotForReplication
,ObjectProperty(sf.constid, 'CnstIsDeleteCascade') as isDeleteCascade
,ObjectProperty(sf.constid, 'CnstIsUpdateCascade') as isUpdateCascade
,so1.object_id as id from sysforeignkeys sf
join sys.objects so1 on sf.constid=so1.object_id
join sys.objects so2 on sf.fkeyid=so2.object_id
join sys.objects so3 on sf.rkeyid=so3.object_id
join sys.schemas su1 on su1.schema_id=so1.schema_id
join sys.schemas su2 on su2.schema_id=so2.schema_id
join sys.schemas su3 on su3.schema_id=so3.schema_id
join syscolumns sc2 on sc2.colid=sf.fkey and sc2.id=so2.object_id
join syscolumns sc3 on sc3.colid=sf.rkey and sc3.id=so3.object_id|||Thanks You ALL for your reply.

i got that using
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

with CTEsql

Find People Names in Long Text

Thank you for taking the time to read this, I need all the advise and help I can get on this ... so please post anything you think would work ... A little confused I am:

Have a database table called "people" with "person name" and "ID" field. My ASP.NET application mainly stores articles inarticle table. An article's Article text mentions various people's names in different combinations (e.g. John, Smith, John Smith, Smith John, etc)

Is there any way, I could compare the article text stored in article table with people table and get the people from people table along with their ID's who have been mentioned in that article? ... so in an article "i love john smith ... and i think Mr smith has always been helpful", I get John Smith back...

Not too sure being honest, what is the best way of implementing this, looking for the most efficient way, probably using XML? SQL Query or may be ASP.NET's code behind?

Thanks once again for taking the time.

Cheers,

Tyro

I think that the best way is stored procedure on SQL server side. The question is

Would you like to do it for single article or for all articles in your database at one run?

If for all names in all articles you will kill you server.

Thanks

|||

Thanks for your reply Jpazgier,

Killing the server is what I am worried about, plus the fact that the people's table is highly likely to grow in the future. I was thinking SQL too. doing some sort of lookup and compare, but think that would be very resource intensive.

I was thinking along the lines of probably being able to compare two XML files ... ?, writing the XML files overnight and then comparing with article text before saving the article in db ... just an idea ... what do you think? please feel free to correct me ..

Tyro

|||

I would create stored procedure which will grabperson namefrom your peopletable and will look for each components of this person name in article field, if it will find all components in article I would just report this person as existing inside article and report its ID. The question is you would like to report the person exists in article if one component of his name is in it or all elements should exists in article to report person as connected to it.

I would run it one time for all articles and next every time you add new person or new article, so maybe you need two or three procedures one for searching multiple article with one person , second to search single article with multiple persons and maybe 3rd for startup and maintenance to search all articles with all persons which are currently in database.

I would store results in table with 3 columns ID, articleID and personID so will be easy to insert results and manipulate it in the future.

Thanks

|||

Thanks for all your replies and helping me out with this Jpazgier, Appreciate it. Hopefully what you said should work well.

Tyro

Find Countries.. MDX

I want to get all the country names from a cube where another Key figure is NOT equal spaces or zeros. Please let me know if some one can help me.

My Cube is CFS_PERF/CFS_PERF_CFSPERF_GL
Want to get 0PAYER_COUNTRY where

[Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] is NOT equal to zero

Please help !!!

Try this:

SELECT [Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] ON 0,

NON EMPTY [OPAYER_COUNTRY].members ON 1

FROM CFS_PERF_CFSPERF_GL

Find column names used in TSQL and Views

Is there are way to find out which View and Stored Procs contain a column
name or even a string? I am trying to rename a columnin a table from
Cust_name to CustName and want to find what views/stored procs will crash.
Thanks in advance,
Stewart Rogers
DataSort Software, L.C.
How do I find a stored procedure containing <text>?
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
AMB
"Datasort" wrote:

> Is there are way to find out which View and Stored Procs contain a column
> name or even a string? I am trying to rename a columnin a table from
> Cust_name to CustName and want to find what views/stored procs will crash.
> Thanks in advance,
> --
> Stewart Rogers
> DataSort Software, L.C.
|||looks like AMesa has you covered for sprocs.
This query will get you the views:
use pubs
go
select v.name
from sys.columns c (nolock) inner join sys.views v (nolock) on v.object_id =
c.object_id
where c.name = 'title'
TheSQLGuru
President
Indicium Resources, Inc.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A3083FBC-6C03-4A28-8B48-F21D8297D3C4@.microsoft.com...[vbcol=seagreen]
> How do I find a stored procedure containing <text>?
> http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
>
> AMB
>
> "Datasort" wrote:

Find column names used in TSQL and Views

Is there are way to find out which View and Stored Procs contain a column
name or even a string? I am trying to rename a columnin a table from
Cust_name to CustName and want to find what views/stored procs will crash.
Thanks in advance,
--
Stewart Rogers
DataSort Software, L.C.How do I find a stored procedure containing <text>?
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
AMB
"Datasort" wrote:
> Is there are way to find out which View and Stored Procs contain a column
> name or even a string? I am trying to rename a columnin a table from
> Cust_name to CustName and want to find what views/stored procs will crash.
> Thanks in advance,
> --
> Stewart Rogers
> DataSort Software, L.C.|||looks like AMesa has you covered for sprocs.
This query will get you the views:
use pubs
go
select v.name
from sys.columns c (nolock) inner join sys.views v (nolock) on v.object_id =c.object_id
where c.name = 'title'
TheSQLGuru
President
Indicium Resources, Inc.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A3083FBC-6C03-4A28-8B48-F21D8297D3C4@.microsoft.com...
> How do I find a stored procedure containing <text>?
> http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
>
> AMB
>
> "Datasort" wrote:
>> Is there are way to find out which View and Stored Procs contain a column
>> name or even a string? I am trying to rename a columnin a table from
>> Cust_name to CustName and want to find what views/stored procs will
>> crash.
>> Thanks in advance,
>> --
>> Stewart Rogers
>> DataSort Software, L.C.

Find an associated user

I am trying to copy a database that is on a Windows 2000 box to one
that is on Windows 2003. Is there a way to export a list of server
login names? I am having problems with one I am trying to create where
on the old server using Teratrax I can see that it is an associated
user Name, but I don't know which user it is associated with. Is there
a command that can tell me which user the account is associated with?
It definitely isn't a domain user and isn't a local account. It says
that the user is a SQl user but I can't seem to find where the
association is.
hi Mark,
"Mark Constant" <constants@.mix-net.net> ha scritto nel messaggio
news:ce43fdea.0412011310.6ac9ef82@.posting.google.c om
> I am trying to copy a database that is on a Windows 2000 box to one
> that is on Windows 2003. Is there a way to export a list of server
> login names? I am having problems with one I am trying to create where
> on the old server using Teratrax I can see that it is an associated
> user Name, but I don't know which user it is associated with. Is there
> a command that can tell me which user the account is associated with?
> It definitely isn't a domain user and isn't a local account. It says
> that the user is a SQl user but I can't seem to find where the
> association is.
SET NOCOUNT ON
USE TEMPDB
PRINT 'adding SQL Server Login [x] to [' + DB_NAME() + '] as [x_user] in the
[db_datareader] database role'
EXEC sp_adduser 'x', 'x_user', 'db_datareader'
PRINT 'grandting [x_user] in [' + DB_NAME() + '] database [db_datawriter]
database role membership'
EXEC sp_addrolemember @.rolename = 'db_datawriter' , @.membername = 'x_user'
PRINT 'listing all [' + DB_NAME() + '] database users'
EXEC sp_helpuser
PRINT 'listing [' + DB_NAME() + '] database [x_user] user''s database role
membership'
EXEC sp_helpuser 'x_user'
PRINT 'Dropping [x_user] user from [' + DB_NAME() + '] database'
EXEC sp_dropuser 'x_user'
which results to
adding SQL Server Login [x] to [tempdb] as [x_user] in the [db_datareader]
database role
Granted database access to 'x'.
'x_user' added to role 'db_datareader'.
grandting [x_user] in [tempdb] database [db_datawriter] database role
membership
'x_user' added to role 'db_datawriter'.
listing all [tempdb] database users
UserName GroupName LoginName DefDBName UserID SID
-- -- -- -- -- --
----
dbo db_owner sa master 1 0x01
x_user db_datareader x a 5
0x67F945E170B2574AA8DCB909B68D46A9
x_user db_datawriter x a 5
0x67F945E170B2574AA8DCB909B68D46A9
listing [tempdb] database [x_user] user's database role membership
UserName GroupName LoginName DefDBName UserID SID
-- -- -- -- -- --
----
x_user db_datareader x a 5
0x67F945E170B2574AA8DCB909B68D46A9
x_user db_datawriter x a 5
0x67F945E170B2574AA8DCB909B68D46A9
Dropping [x_user] user from [tempdb] database
User has been dropped from current database.
as you can see you can inspect the result of sp_helpuser system store
precedure
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_sp_help_45o2.asp) that reports, as long as the user name, the corresponding
Login..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

find all tables that a table is related to

how can i find all the names of tables that a specific table is related to in tsql?

sp_depends

Displays information about database object dependencies (for example, the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure). References to objects outside the current database are not reported.

Examples

This example lists the database objects that depend on the Customers table.

USE Northwind

EXEC sp_depends 'Customers'

|||

Assuming you have referential integrity in place you can also query the sys.sysforeignkeys view in 2005 or the sysforeignkeys table in 2000. Here is a query returning all table associations (2005 syntax, to use in 2000 remove "sys." prefix):

SELECT

o.name [parentTable]

,o2.name [childTable]

FROM

sys.sysobjects o

INNER JOIN sys.sysforeignkeys fk ON fk.rkeyid = o.id

INNER JOIN sys.sysobjects o2 ON o2.id = fk.fkeyid AND o2.id <> o.id

WHERE

o.name = 'myTable'

Filtering Table name with Parameter

<br><br>I obtain table names from a database and pass them to a dropdownlist. Based on user selection, I want to pass each table name to a query.Here is an extract from my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="select * from @.dDTable"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="dDTable" PropertyName="SelectedValue" DefaultValue="product" Direction="InputOutput" Size="15" Type="String" /> </SelectParameters> </asp:SqlDataSource>I keep getting this error: Must declare the table variable "@.dDTable".Please does anyone knows the best way to go about this?

Hi,

thats not working. Dynamic table name are allowed in here. You could use a stored procedure instead which then uses dynamic sql to execute the query that was first concatenated (with the table name).

But you should be aware of the curse of dynamic SQL :-)

http://www.sommarskog.se/dynamic_sql.html

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks.