Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.
S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegr oups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>sql

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

finding columns

In a specific database. I would like to check to see if a particular column
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and Query Analyzer for example. You can do it programatically via ADO.OpenSchema(), sp_help, or DMO... Of course querying the system tables is always an option -- something like (this doesn't narrow to the database level)
select so.name as 'Table', sc.Name as 'Column
from syscolumns s
join sysobjects so on so.id = sc.i
wher
xo.xtype = 'u
--an
--sc.name like '%column_name_to_find%
group by so.name,sc.nam
...the usual caveats apply (MS does not recommend using the system tables, etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > In a specific database. I would like to check to see if a particular
> column
> > exists in all my tables i.e user created tables. I know I have to use
the
> > sysobjects and syscolumns table. But I am sorta at loss to find out how
> they
> > are related.
> >
> > Please Help
> >
> > VJ
> >
> >
>

finding columns

In a specific database. I would like to check to see if a particular column
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and
Query Analyzer for example. You can do it programatically via ADO.OpenSche
ma(), sp_help, or DMO... Of course querying the system tables is always an
option -- something like (t
his doesn't narrow to the database level):
select so.name as 'Table', sc.Name as 'Column'
from syscolumns sc
join sysobjects so on so.id = sc.id
where
xo.xtype = 'u'
--and
--sc.name like '%column_name_to_find%'
group by so.name,sc.name
...the usual caveats apply (MS does not recommend using the system tables,
etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> column
the
> they
>

Finding all views with a specific name, in all databases

Hi,

We are migrating to SQL Server 2005 from 2000 and we have a view created in many of our databases. That view must be changed after the migration because it uses system tables and does not return the correct information in SQL Server 2005.

To do that, I want to create an SSIS-package that loops through all the databases on a particular server. If that view exists in the database, I want to run a script that change the view.

What I try to do:

Set up a For Each Loop container with Foreach SMO Enumerator

Set the connection to my server

Set the Enumerate property to "SMOEnumObj[@.Name='Databases']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place Index 0 in Variable User::dbName

In the For Each Loop, place a script task to msgbox the value of User::dbName

This all works good. The problem comes when I try to nest the For Each Loop

Inside the "For Each Database Loop", place a new For Each Loop container with Foreach SMO Enumerator

I connect the Msgbox Script Task with the new For each loop

Use the same connection

Set the EnumURN property in the Expression Builder to "Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place index 0 in Variable User::tabName

In the For Each Loop, place a script task to msgbox the value of User::tabName

When I try to run the package now, it does not at all.

In my Progress tab I see that Validation has started and completed for each task, but I see now errors. Nothing has run.

Test 1: I change the DelayValidation for the Inner Loop to True

Now the package runs, but I never get to the script task in the inner loop.

Question 1: What's the problem?

Question 2: Is there another way to do this?

Regards

Magnus

Have you tried using a second connection? It seems like the above should work for you, but maybe there's an issue with the SMO Enumerator that requires a different connection for the inner loop.|||

Hi Sean, and thanks for your response.

I tried to use a second connection, but I still get the same result.

/Magnus

|||Forget the SMO enumerator in your Foreach loop.

Do an Execute SQL Task and use something like:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'VIEWNAME%'

Then set the resultset to full result set and create an object type var to hold the results. Then loop thru the results using the ForEach ADO.

See this example. I found it very helpful.

http://www.cubido.at/Blog/tabid/176/EntryID/59/Default.aspx|||

Hi Tom, I tried your suggestion and it worked alright. Thanks!

Because I only expect a single row, I used a Single Row instead of Full ResultSet and used a SQL like

SELECT ISNULL(Max(TABLE_NAME), '') AS TableName FROM [databasename].INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewName'

I set the SQLStatement with an expression since I needed to change the databasename for each iteration. I placed the field TableName in a variable. Before I move on to the next step, where I want to alter the view, I use a Constraint and Expression constraint and make sure that the variable is not empty.

Then I create an Execute SQL task to alter the view. I use the same connection as in the above task and use an expression to create the SQLStatement to something like:

USE [databasename]
GO

ALTER VIEW [dbo].[viewName]
AS
SELECT *
FROM theTable

/Magnus

|||

I had the same problem except I wasn't using SMO Enumeration for the outter loop. You can't just use set the EnumURN property in the Expression Builder to...
"Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Your missing two parts. You have to set the Expression to...
"RuntimeServer[@.Name='<the name of your SMO Connection in the Connection Manager>']/Server[@.Name='<the server name>']/Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Hope this at least helps someone down the road. It took me 4 hours to figure it out! Either I'm retarded or it's not explained well.

sql

Finding all views with a specific name, in all databases

Hi,

We are migrating to SQL Server 2005 from 2000 and we have a view created in many of our databases. That view must be changed after the migration because it uses system tables and does not return the correct information in SQL Server 2005.

To do that, I want to create an SSIS-package that loops through all the databases on a particular server. If that view exists in the database, I want to run a script that change the view.

What I try to do:

Set up a For Each Loop container with Foreach SMO Enumerator

Set the connection to my server

Set the Enumerate property to "SMOEnumObj[@.Name='Databases']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place Index 0 in Variable User::dbName

In the For Each Loop, place a script task to msgbox the value of User::dbName

This all works good. The problem comes when I try to nest the For Each Loop

Inside the "For Each Database Loop", place a new For Each Loop container with Foreach SMO Enumerator

I connect the Msgbox Script Task with the new For each loop

Use the same connection

Set the EnumURN property in the Expression Builder to "Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place index 0 in Variable User::tabName

In the For Each Loop, place a script task to msgbox the value of User::tabName

When I try to run the package now, it does not at all.

In my Progress tab I see that Validation has started and completed for each task, but I see now errors. Nothing has run.

Test 1: I change the DelayValidation for the Inner Loop to True

Now the package runs, but I never get to the script task in the inner loop.

Question 1: What's the problem?

Question 2: Is there another way to do this?

Regards

Magnus

Have you tried using a second connection? It seems like the above should work for you, but maybe there's an issue with the SMO Enumerator that requires a different connection for the inner loop.|||

Hi Sean, and thanks for your response.

I tried to use a second connection, but I still get the same result.

/Magnus

|||Forget the SMO enumerator in your Foreach loop.

Do an Execute SQL Task and use something like:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'VIEWNAME%'

Then set the resultset to full result set and create an object type var to hold the results. Then loop thru the results using the ForEach ADO.

See this example. I found it very helpful.

http://www.cubido.at/Blog/tabid/176/EntryID/59/Default.aspx|||

Hi Tom, I tried your suggestion and it worked alright. Thanks!

Because I only expect a single row, I used a Single Row instead of Full ResultSet and used a SQL like

SELECT ISNULL(Max(TABLE_NAME), '') AS TableName FROM [databasename].INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewName'

I set the SQLStatement with an expression since I needed to change the databasename for each iteration. I placed the field TableName in a variable. Before I move on to the next step, where I want to alter the view, I use a Constraint and Expression constraint and make sure that the variable is not empty.

Then I create an Execute SQL task to alter the view. I use the same connection as in the above task and use an expression to create the SQLStatement to something like:

USE [databasename]
GO

ALTER VIEW [dbo].[viewName]
AS
SELECT *
FROM theTable

/Magnus

|||

I had the same problem except I wasn't using SMO Enumeration for the outter loop. You can't just use set the EnumURN property in the Expression Builder to...
"Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Your missing two parts. You have to set the Expression to...
"RuntimeServer[@.Name='<the name of your SMO Connection in the Connection Manager>']/Server[@.Name='<the server name>']/Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Hope this at least helps someone down the road. It took me 4 hours to figure it out! Either I'm retarded or it's not explained well.

Find the nonexisting Event

Hello everyone I am pretty new with T-SQL and SQL Server 2000 and I need some help or some suggestions on a specific problem. Here goes...

I have 2 tables that Iam using

EVENTS Event_Codes

PersonIdNo EventCodeIdNo EventCodeIdNo EvenCode

6349 13 13 Criminal History

6349 31 31 DL

6349 30 30 CDL

6345 75 75 EMPLOYMENT REF

6345 13 74 Texas Lic

2 CDA

4 Emp Suggestions

I need to find the EventCodeIdNo's that are missing for each PERSON in the Events Table. I've tried creating Arrays and looping through them but I haven't had much success with that.

Thank you,

-DM

Code Snippet

There might be better solutions, if the number of rows are small in events then you could use something like this.

select distinct PersonIdNo, ce.EventCodeIdNo, ce.EvenCode from
(select
PersonIdNo, ec.EventCodeIdNo, ec.EvenCode
from events e
cross join Event_Codes ec) ce
where not exists
(select * from events where events.PersonIdNo = ce.PersonIdNo
and events.EventCodeIdNo = ce.EventCodeIdNo)

|||

Well, assuming you also have a person table, you take a cross join of the persons and events, then remove the ones where there is a match.

drop table eventAttendee, event, person
go
create table person --added because a person might not have attended an event at all
(
personId int primary key
)
create table event
(
eventCode int primary key
)
create table eventAttendee
(
personId int references person(personId),
eventCode int references event(eventCode),
primary key (personId, eventCode)
)
insert into person
select 6349
union all
select 6345
insert into event
select 13
union all
select 31
union all
select 30
union all
select 75
union all
select 74
union all
select 2
union all
select 4

insert into eventAttendee
select 6349, 13
union all
select 6349, 31
union all
select 6349, 30
union all
select 6345, 75
union all
select 6345, 13

select *
from event
cross join person
where not exists ( select *
from eventAttendee
where event.eventCode = eventAttendee.eventCode
and person.personId = eventAttendee.personId)

|||

Louis has provided a nice elegant solution.

I offer this suggestion as an alternative in case you don't wish to involve the Person Table in the query. Depending upon indexing, this should be relatively quick.

SET NOCOUNT ON

DECLARE @.Events table
( RowID int IDENTITY,
PersonID int,
EventCodeID int
)

INSERT INTO @.Events VALUES ( 6349, 13 )
INSERT INTO @.Events VALUES ( 6349, 31 )
INSERT INTO @.Events VALUES ( 6349, 30 )
INSERT INTO @.Events VALUES ( 6345, 75 )
INSERT INTO @.Events VALUES ( 6345, 13 )

DECLARE @.EventCodes table
( RowID int IDENTITY,
EventCodeID int,
EventCode varchar(25)
)

INSERT INTO @.EventCodes VALUES ( 13, 'Criminal History' )
INSERT INTO @.EventCodes VALUES ( 31, 'DL' )
INSERT INTO @.EventCodes VALUES ( 30, 'CDL' )
INSERT INTO @.EventCodes VALUES ( 75, 'EMPLOYMENT REF' )
INSERT INTO @.EventCodes VALUES ( 74, 'Texas Lic' )
INSERT INTO @.EventCodes VALUES ( 2, 'CDA' )
INSERT INTO @.EventCodes VALUES ( 4, 'Emp Suggestions' )

SELECT DISTINCT
dt.PersonID,
dt.EventCodeID,
dt.EventCode
FROM @.Events e
RIGHT JOIN (SELECT DISTINCT
e1.PersonID,
ec.EventCodeID,
ec.EventCode
FROM @.Events e1
CROSS JOIN @.EventCodes ec
) dt
ON ( e.EventCodeID = dt.EventCodeID
AND e.PersonID = dt.PersonID

)
WHERE e.PersonID IS NULL
ORDER BY
dt.PersonID,
EventCodeID

PersonID EventCodeID EventCode
-- -- -
6345 2 CDA
6345 4 Emp Suggestions
6345 30 CDL
6345 31 DL
6345 74 Texas Lic
6349 2 CDA
6349 4 Emp Suggestions
6349 74 Texas Lic
6349 75 EMPLOYMENT REF

|||

Here I give both fast & slow one. According to your data-volume which ever fits use it,

Code Snippet

Create Table #event_codes (

[EventCodeIdNo] int primary key ,

[EvenCode] Varchar(100)

);

Insert Into #event_codes Values('13','Criminal-History');

Insert Into #event_codes Values('31','DL');

Insert Into #event_codes Values('30','CDL');

Insert Into #event_codes Values('75','EMPLOYMENT-REF');

Insert Into #event_codes Values('74','Texas-Lic');

Insert Into #event_codes Values('2','CDA');

Insert Into #event_codes Values('4','Emp-Suggestions');

Create Table #events (

[PersonIdNo] int ,

[EventCodeIdNo] int primary key([PersonIdNo],[EventCodeIdNo])

);

Insert Into #events Values('6349','13');

Insert Into #events Values('6349','31');

Insert Into #events Values('6349','30');

Insert Into #events Values('6345','75');

Insert Into #events Values('6345','13');

Code Snippet

--complex & slow one

select

pes.[PersonIdNo],

pes.[EventCodeIdNo],

pes.[EvenCode]

from

#events es

full outer join

(select Distinct es.[PersonIdNo],ec.[EventCodeIdNo] ,ec.[EvenCode]

from #events es

cross join #event_codes ec) as pes

On pes.[PersonIdNo]=es.[PersonIdNo]

and pes.[EventCodeIdNo] = es.[eventcodeidno]

Where

es.[EventCodeIdNo] is null

Code Snippet

--fast & good one

select

*

from

#event_codes ec

cross join (select distinct [personidno] from #events) p

where

NOT exists

(

select 1 from #events es

where es.[eventcodeidno] = ec.[eventcodeidno]

and es.[personidno]=p.[personidno]

)

|||

Thank you all for your kind suggestions. I went ahead and used Sankar Reddy's suggestions because I have way to many person id and event combos to insert into temp tables. Thanks again for all your suggestions this is a great community!

-Dm

find text string in database

Hello,
I'd like to find a specific text string searching in all tables within same database.
Is there a way to make only one query to all tables at the same time?
Thank you very much for your attention.
QslxNo. You would have to write a procedure that looped through all the tables and checked every column.

This question makes me suspect that there are some design issues with your database schema.

blindman|||Ya mean like:

USE Northwind
GO

CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)
GO

SET NOCOUNT ON

DECLARE @.SQL varchar(8000), @.TABLE_NAME sysname, @.COLUMN_NAME sysname, @.Sargable varchar(80), @.Count int

SELECT @.Sargable = 'Beer'

DECLARE insaneCursor CURSOR FOR
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ')
AND t.TABLE_TYPE = 'BASE TABLE'

OPEN insaneCursor

FETCH NEXT FROM insaneCursor INTO @.TABLE_NAME, @.COLUMN_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '
+ '''' + @.TABLE_NAME + '''' + ','
+ '''' + @.COLUMN_NAME + '''' + ','
+ 'COUNT(*) FROM [' + @.TABLE_NAME
+ '] WHERE [' + @.COLUMN_NAME + '] Like '
+ ''''+ '%' + @.Sargable + '%' + ''''
--SELECT @.SQL
EXEC(@.SQL)
IF @.@.ERROR <> 0
BEGIN
SELECT @.SQL
SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @.TABLE_NAME
GOTO Error
END
FETCH NEXT FROM insaneCursor INTO @.TABLE_NAME, @.COLUMN_NAME
END

SELECT * FROM myTable99 WHERE Occurs <> 0

Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor

GO

DROP TABLE myTable99
GO

SET NOCOUNT OFF|||Hi Brett,
Thanks a lot for you help.
It works great!

Cheers,|||brett, don't you have any hobbies? :p|||Yeah...SQL

That was a cut and paste from my toolbox...

You kidding...I'm writting a sql server version of the window explorer find function...using xp_cmdshell, because it's tooooooooooooo painful to deal with server ops...|||I can't tell when you're kidding and when you're not!|||I'd say I'm an Enigma...but that's taken already...8-)

And I finally got server ops to give me clearence, so I won't have to build the explorer

Are Margarittas a hobby?

Find specific text in a string

Hai ,
in a textbox am having text as
"(20/100)+pay1*pay2" .it's a formula. and stored in a particular
variable.
string strformula="(20/100)+pay1*pay2" ;
i've to substitute the value of the variable 'pay1' & 'pay2' and
finding the value of that strformula.
can any onr tell me how to find 'pay1' and 'pay2' in the variable
strformula. it's urgent and reply immediately.
Thanks in advance.Hi
I am not sure what this has to do with SQL Server!
If the strings are unique then you could use the replace function.
John
<ksrajalakshmi@.gmail.com> wrote in message
news:1139642884.262422.60580@.g14g2000cwa.googlegroups.com...
> Hai ,
> in a textbox am having text as
> "(20/100)+pay1*pay2" .it's a formula. and stored in a particular
> variable.
> string strformula="(20/100)+pay1*pay2" ;
> i've to substitute the value of the variable 'pay1' & 'pay2' and
> finding the value of that strformula.
> can any onr tell me how to find 'pay1' and 'pay2' in the variable
> strformula. it's urgent and reply immediately.
> Thanks in advance.
>|||If am having the value as
string strvalue ="(12.23+233.56)*12/100";
i've to find the value.so that am converting to double. but it throws
error. tel me how to find value?|||Hi
It is still not clear if you are using SQL Server! If you are then you can
do something like:
DECLARE @.strformula nvarchar(60)
DECLARE @.nparams nvarchar(80)
DECLARE @.output decimal(10,4)
DECLARE @.pay1 decimal(10,4)
DECLARE @.pay2 decimal(10,4)
SET @.strformula = N'SELECT @.output_val = (20.0/100)+(@.pay_1*@.pay_2)'
SET @.nparams = N'@.output_val decimal(10,4) OUTPUT, @.pay_1 decimal(10,4),
@.pay_2 decimal(10,4)'
SET @.pay1 = 233.56
SET @.pay2 = 12.0/100
SELECT @.strformula
SELECT @.nparams
EXEC sp_executesql @.strformula, @.nparams, @.output_val = @.output OUTPUT,
@.pay_1 = @.pay1, @.pay_2 = @.pay2
SELECT @.output
John
<ksrajalakshmi@.gmail.com> wrote in message
news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
> If am having the value as
> string strvalue ="(12.23+233.56)*12/100";
> i've to find the value.so that am converting to double. but it throws
> error. tel me how to find value?
>|||Actually I meant that your syntax wasn't SQL syntax:

Not mine. It is T-SQL :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message news:...
> First, this clearly isn't SQL Syntax, but that notwithstanding, you can
> evaluate a function like this, (as long as it fits SQL Syntax of course)
> declare @.value decimal (10,8)
> declare @.formula varchar(200), @.query nvarchar(2000)
> set @.formula = '(12.23+233.56)*12/100'
> set @.query = 'select @.value = (' + @.formula + ')'
> EXEC sp_executesql @.query,
> N'@.Value decimal(10,8) output',
> @.value output
> select @.value
> I would strongly suggest against it, since this is really not SQL's
> strongpoint. This is one of the rare cases where I would probably suggest
> you storing the expression and the answer in two columns (using the middle
> tier layer to calculate the value, or this method could be used in a a
> singleton insert.)
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> <ksrajalakshmi@.gmail.com> wrote in message
> news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
>|||First, this clearly isn't SQL Syntax, but that notwithstanding, you can
evaluate a function like this, (as long as it fits SQL Syntax of course)
declare @.value decimal (10,8)
declare @.formula varchar(200), @.query nvarchar(2000)
set @.formula = '(12.23+233.56)*12/100'
set @.query = 'select @.value = (' + @.formula + ')'
EXEC sp_executesql @.query,
N'@.Value decimal(10,8) output',
@.value output
select @.value
I would strongly suggest against it, since this is really not SQL's
strongpoint. This is one of the rare cases where I would probably suggest
you storing the expression and the answer in two columns (using the middle
tier layer to calculate the value, or this method could be used in a a
singleton insert.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<ksrajalakshmi@.gmail.com> wrote in message
news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
> If am having the value as
> string strvalue ="(12.23+233.56)*12/100";
> i've to find the value.so that am converting to double. but it throws
> error. tel me how to find value?
>

Find out whether a database is merge subscriber

Hi,
maybe someone can help me with this one.
I need to find out via T-SQL whether a specific database is a subscriber
of a merge publication. The publisher might not be available when
querying this.
I would have expected bit 4 (value 8) of
master.dbo.sysdatabases.category to be set; however, it isn't in my
replicated databases (maybe just for snapshot replication?)
Thanks for any help on this!
Roland
Roland,
you should be able to find the publication in sysmergesubscriptions on the
subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

find out what tables contain a specific column

I want to write SQL that will search the tables in a database for a specific column, like this. For instance, I have a column "Unique_ID" that is in many of our tables (hundreds) but not in others and want to find out the tables it is in. It is always the first column.

I tried to find a system stored procdure to do this but couldn't and tried to create a script using the sysobjects and syscolumns tables in the Master db, but came to a roadblock because they don't seem to be related at all.

I would surely appreciate if someone else has already done this!

Thanks!

When I use the sysobjects/syscolumns to get info about a db I always do it in the DB I need info about, not in the master table.

use MyDatabase;
select * from sysobjects where name like 'someprefix%'

To get some info it works well but not recommended to use in production.

|||

SELECT a.TABLE_NAME, b.COLUMN_NAME, b.ORDINAL_POSITION

FROM INFORMATION_SCHEMA.TABLES a JOIN INFORMATION_SCHEMA.COLUMNS b

ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_NAME LIKE 'TblName%'

AND b.COLUMN_NAME LIKE 'ColName%'

ORDER BY a.TABLE_NAME

|||Nope, this one yielded no results. I'll try the next one and let you all know. Thanks for trying!|||WONDERFUL! Just what I was looking for. Thank you so much!|||

Try this way. If you still don't get anything, then you have other "issues".

SELECT a.TABLE_NAME, b.COLUMN_NAME, b.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES a JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.TABLE_NAME = b.TABLE_NAME
ORDER BY a.TABLE_NAME

Find and Replace string value in a stored procedure.

Not sure if there is a way to do this programmatically. I am using SQL
Server 2005 and I need to find a specific string value in a stored
procedure and replace that string value with some other value.
I can perform the "Find" part of the process by querying the
Information_Schema.Routines view but not sure there is a way to
perform the replace, essentially ALTERING the stored procedure. Is
there a command I can use that can do this? I essentially want to do
a batch Find and Replace type process.
Thanks in advance!Safest way is to use the code files you have stored in your source code
control system. Check them all out, do a find/replace in files command
(using a tool such as Search and Replace), check them back in. Compile the
ones that had a replacement. S&R can provide a file listing that can be
used to drive a compile process using sqlcmd or numerous other mechanisms.
If you don't have a system like the above, take the time to set one up! :-)
You can easily script all sprocs to separate files using SSMS or many other
3rd party tools. I like ApexSQL's Script.
<lgalumbres@.gmail.com> wrote in message
news:1191591082.224636.49880@.o80g2000hse.googlegroups.com...
> Not sure if there is a way to do this programmatically. I am using SQL
> Server 2005 and I need to find a specific string value in a stored
> procedure and replace that string value with some other value.
> I can perform the "Find" part of the process by querying the
> Information_Schema.Routines view but not sure there is a way to
> perform the replace, essentially ALTERING the stored procedure. Is
> there a command I can use that can do this? I essentially want to do
> a batch Find and Replace type process.
> Thanks in advance!
>

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'

Find all references to a database

Is there a way to scan all procedures, functions and triggers in an SQL
server for references to a specific database?
Thanks
Tom G.The following technique isn't perfect but will identify most objects. You
could also script the textual objects to a file and use a find command.
USE MyDatabase
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%SomeDatabase%'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Groszko" <newscorrespondent@.charter.net> wrote in message
news:emfU1rr1DHA.1184@.TK2MSFTNGP10.phx.gbl...
> Is there a way to scan all procedures, functions and triggers in an SQL
> server for references to a specific database?
> Thanks
> Tom G.
>|||The solution I have come up with is:
Create a table like this:
create table PAFCONVERSION (
DBNAME sysname not null,
OBJECTNAME sysname not null,
OBJECTYPE char(2) null,
constraint PK_PAFCONVERSION primary key (DBNAME, OBJECTNAME)
)
Run this in query analyzer, copy the output pane to the execution pane and
run it.
SET NOCOUNT ON
USE master
go
DECLARE DBCURSOR CURSOR FAST_FORWARD FOR
SELECT NAME FROM SYSDATABASES ORDER BY NAME FOR READ ONLY
DECLARE @.DBNAME SYSNAME
OPEN DBCURSOR
FETCH DBCURSOR INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE @.DBPROCESS varchar(8000)
SET @.DBPROCESS = 'USE ' + @.DBNAME + CHAR(10) + 'GO' + CHAR(10)
+ 'INSERT SDRMONITOR.DBO.PAFCONVERSION (DBNAME, OBJECTNAME, OBJECTYPE)' +
CHAR(10)
+ 'SELECT DISTINCT ''' + @.DBNAME + ''', SYSOBJECTS.NAME, SYSOBJECTS.XTYPE'
+ CHAR(10)
+ 'FROM syscomments' + CHAR(10)
+ 'JOIN SYSOBJECTS ON (SYSCOMMENTS.ID = SYSOBJECTS.ID)' + CHAR(10)
+ 'where text like (''%what you are looking for%'') ' + CHAR(10)
+ 'ORDER BY SYSOBJECTS.NAME, SYSOBJECTS.XTYPE' + CHAR(10)
+ 'GO' + CHAR(10)
SELECT @.DBPROCESS
FETCH DBCURSOR INTO @.DBNAME
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
GO
"Tom Groszko" <newscorrespondent@.charter.net> wrote in message
news:emfU1rr1DHA.1184@.TK2MSFTNGP10.phx.gbl...
> Is there a way to scan all procedures, functions and triggers in an SQL
> server for references to a specific database?
> Thanks
> Tom G.
>

find a field in a database

hello,
i want to search all of the databases on a sql server for a specific field,
e.g. last name, or ssn, what would be the best way to do that?
thanks
-- Note, those are doubled single quotes (') around <column name>
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/

> hello,
> i want to search all of the databases on a sql server for a specific
> field,
> e.g. last name, or ssn, what would be the best way to do that?
> thanks
>
|||Let's try that again for those clients reading it as HTML...
-- Note, those are doubled single quotes (') around ** Column Name **
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''** Column Name **'''
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/
|||thanks, i appreciate it, i will try this out. i was expecting there was a
"right click" search trick.
"K. Brian Kelley" wrote:

> -- Note, those are doubled single quotes (') around <column name>
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
|||jason,
If you're on SQL 2K, use the attached query and plug in the column name you
want.
-- Bill
"jason" <jason@.discussions.microsoft.com> wrote in message
news:AA1A9F0A-ACD7-4F28-B84C-CF70731512AB@.microsoft.com...
> hello,
> i want to search all of the databases on a sql server for a specific
> field,
> e.g. last name, or ssn, what would be the best way to do that?
> thanks
>
|||Hi Brian,
i must be doing something wrong, the query runs, but returns blank rows.
Meaning there are many header rows but no data. I don't expect the db
context matters, but I tried master, model, msdb and some user dbs. same
result. the exact query i am running is.
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Subject'''
I also tried it with the < > around subject ''<Subject>''' to be thorough,
same result. I know that there is a column named Subject, I just drilled
into a table to find one for a test.
there are not messages returned either.
"K. Brian Kelley" wrote:

> -- Note, those are doubled single quotes (') around <column name>
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
|||Actually I see that if i set the context db to the db where i know the column
exists, i then see the tables returned for that db...but it returns the same
tables 45 times, the same number of databases on this instance.
"jason" wrote:
[vbcol=seagreen]
> Hi Brian,
> i must be doing something wrong, the query runs, but returns blank rows.
> Meaning there are many header rows but no data. I don't expect the db
> context matters, but I tried master, model, msdb and some user dbs. same
> result. the exact query i am running is.
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Subject'''
> I also tried it with the < > around subject ''<Subject>''' to be thorough,
> same result. I know that there is a column named Subject, I just drilled
> into a table to find one for a test.
> there are not messages returned either.
> "K. Brian Kelley" wrote:
|||Hello Jason,
You may want to add "Use ?" before the select query:
EXEC sp_MSforeachDB 'use ?; SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = "accountid"'
Hope this helps.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||thanks, this is exactly what i needed, i was trying to work the "use ?" into
it, but was going about a different way and it wasn't working.
"Peter Yang [MSFT]" wrote:

> Hello Jason,
> You may want to add "Use ?" before the select query:
> EXEC sp_MSforeachDB 'use ?; SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA,
> TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = "accountid"'
> Hope this helps.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>

find a field in a database

hello,
i want to search all of the databases on a sql server for a specific field,
e.g. last name, or ssn, what would be the best way to do that?
thanks-- Note, those are doubled single quotes (') around <column name>
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/
> hello,
> i want to search all of the databases on a sql server for a specific
> field,
> e.g. last name, or ssn, what would be the best way to do that?
> thanks
>|||thanks, i appreciate it, i will try this out. i was expecting there was a
"right click" search trick.
"K. Brian Kelley" wrote:
> -- Note, those are doubled single quotes (') around <column name>
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
> > hello,
> > i want to search all of the databases on a sql server for a specific
> > field,
> > e.g. last name, or ssn, what would be the best way to do that?
> > thanks
> >
>
>|||Hi Brian,
i must be doing something wrong, the query runs, but returns blank rows.
Meaning there are many header rows but no data. I don't expect the db
context matters, but I tried master, model, msdb and some user dbs. same
result. the exact query i am running is.
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Subject'''
I also tried it with the < > around subject ''<Subject>''' to be thorough,
same result. I know that there is a column named Subject, I just drilled
into a table to find one for a test.
there are not messages returned either.
"K. Brian Kelley" wrote:
> -- Note, those are doubled single quotes (') around <column name>
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
> > hello,
> > i want to search all of the databases on a sql server for a specific
> > field,
> > e.g. last name, or ssn, what would be the best way to do that?
> > thanks
> >
>
>|||Actually I see that if i set the context db to the db where i know the column
exists, i then see the tables returned for that db...but it returns the same
tables 45 times, the same number of databases on this instance.
"jason" wrote:
> Hi Brian,
> i must be doing something wrong, the query runs, but returns blank rows.
> Meaning there are many header rows but no data. I don't expect the db
> context matters, but I tried master, model, msdb and some user dbs. same
> result. the exact query i am running is.
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Subject'''
> I also tried it with the < > around subject ''<Subject>''' to be thorough,
> same result. I know that there is a column named Subject, I just drilled
> into a table to find one for a test.
> there are not messages returned either.
> "K. Brian Kelley" wrote:
> > -- Note, those are doubled single quotes (') around <column name>
> > EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
> > FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
> >
> > K. Brian Kelley, brian underscore kelley at sqlpass dot org
> > http://www.truthsolutions.com/
> >
> > > hello,
> > > i want to search all of the databases on a sql server for a specific
> > > field,
> > > e.g. last name, or ssn, what would be the best way to do that?
> > > thanks
> > >
> >
> >
> >|||Hello Jason,
You may want to add "Use ?" before the select query:
EXEC sp_MSforeachDB 'use ?; SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = "accountid"'
Hope this helps.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||thanks, this is exactly what i needed, i was trying to work the "use ?" into
it, but was going about a different way and it wasn't working.
"Peter Yang [MSFT]" wrote:
> Hello Jason,
> You may want to add "Use ?" before the select query:
> EXEC sp_MSforeachDB 'use ?; SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA,
> TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = "accountid"'
> Hope this helps.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

find a field in a database

hello,
i want to search all of the databases on a sql server for a specific field,
e.g. last name, or ssn, what would be the best way to do that?
thanks-- Note, those are doubled single quotes (') around <column name>
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/

> hello,
> i want to search all of the databases on a sql server for a specific
> field,
> e.g. last name, or ssn, what would be the best way to do that?
> thanks
>|||Let's try that again for those clients reading it as HTML...
-- Note, those are doubled single quotes (') around ** Column Name **
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''** Column Name **'''
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/|||thanks, i appreciate it, i will try this out. i was expecting there was a
"right click" search trick.
"K. Brian Kelley" wrote:

> -- Note, those are doubled single quotes (') around <column name>
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NA
ME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
>|||underprocessable|||Hi Brian,
i must be doing something wrong, the query runs, but returns blank rows.
Meaning there are many header rows but no data. I don't expect the db
context matters, but I tried master, model, msdb and some user dbs. same
result. the exact query i am running is.
EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Subject'''
I also tried it with the < > around subject ''<Subject>''' to be thorough,
same result. I know that there is a column named Subject, I just drilled
into a table to find one for a test.
there are not messages returned either.
"K. Brian Kelley" wrote:

> -- Note, those are doubled single quotes (') around <column name>
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NA
ME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''<column name>'''
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
>|||Actually I see that if i set the context db to the db where i know the colum
n
exists, i then see the tables returned for that db...but it returns the same
tables 45 times, the same number of databases on this instance.
"jason" wrote:
[vbcol=seagreen]
> Hi Brian,
> i must be doing something wrong, the query runs, but returns blank rows.
> Meaning there are many header rows but no data. I don't expect the db
> context matters, but I tried master, model, msdb and some user dbs. same
> result. the exact query i am running is.
> EXEC sp_MSforeachDB 'SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NA
ME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Subject'''
> I also tried it with the < > around subject ''<Subject>''' to be thorough,
> same result. I know that there is a column named Subject, I just drilled
> into a table to find one for a test.
> there are not messages returned either.
> "K. Brian Kelley" wrote:
>|||Hello Jason,
You may want to add "Use ?" before the select query:
EXEC sp_MSforeachDB 'use ?; SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = "accountid"'
Hope this helps.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||thanks, this is exactly what i needed, i was trying to work the "use ?" into
it, but was going about a different way and it wasn't working.
"Peter Yang [MSFT]" wrote:

> Hello Jason,
> You may want to add "Use ?" before the select query:
> EXEC sp_MSforeachDB 'use ?; SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA,
> TABLE_NAME
> FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = "accountid"'
> Hope this helps.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>