Hi,
Does anyone have a script for or know the easiest way to list the indexes in
all tables (for all user databases) in sql server 2000?
Thanks,
AndrewThat's easy. The easiest way to list all indexes for all tables in a DB is:
select * from MyDB.dbo.sysindexes
Of course, it's not very user friendly - you might want to run it from
the actual DB and throw in some functions like object_name([id]). For
example,
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
order by object_name([id]), indid
For more info see the BOL entries on the sysindexes system table. For
instance, indid 0 for a table is the data (ie. there is no clustered
index for that table - it's a heap), index 1 is the clustered index, 2
to 254 are nonclustered indexes, 255 is for text/image data, etc.
Anyway, check out BOL.
Cheers,
Mike.
Andrew wrote:
> Hi,
> Does anyone have a script for or know the easiest way to list the indexes in
> all tables (for all user databases) in sql server 2000?
> Thanks,
> Andrew
>|||Also, thinks like statistics and hypothetical indexes need to be filtered out. Check out the
INDEXPROPERTY function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:%23zIgFhy5EHA.3648@.TK2MSFTNGP11.phx.gbl...
> That's easy. The easiest way to list all indexes for all tables in a DB is:
> select * from MyDB.dbo.sysindexes
> Of course, it's not very user friendly - you might want to run it from the actual DB and throw in
> some functions like object_name([id]). For example,
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> order by object_name([id]), indid
> For more info see the BOL entries on the sysindexes system table. For instance, indid 0 for a
> table is the data (ie. there is no clustered index for that table - it's a heap), index 1 is the
> clustered index, 2 to 254 are nonclustered indexes, 255 is for text/image data, etc. Anyway, check
> out BOL.
> Cheers,
> Mike.
> Andrew wrote:
>> Hi,
>> Does anyone have a script for or know the easiest way to list the indexes in
>> all tables (for all user databases) in sql server 2000?
>> Thanks,
>> Andrew|||OK then, how about (not tested):
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
order by object_name([id]), indid
If you want it for all user DBs you can wrap it in a cursor like:
declare @.cmd nvarchar(4000)
declare @.DBName sysname
declare AllUserDBs cursor for
select [name] from master.dbo.sysdatabases
where [name] not in ('master', 'msdb', 'model', 'tempdb',
'distribution', 'pubs', 'Northwind')
order by [name]
open AllUserDBs
fetch next from AllUserDBs into @.DBName
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'use [@.DB]; ' +
' select @.DB as [DB], object_name([id]), indid, [name] from
dbo.sysindexes ' +
' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
' order by object_name([id]), indid'
exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
end
fetch next from AllUserDBs into @.DBName
end
close AllUserDBs
deallocate AllUserDBs
(Please pardon any typos - this is untested and off the top of my head.)
Cheers,
Mike.
Tibor Karaszi wrote:
> Also, thinks like statistics and hypothetical indexes need to be
> filtered out. Check out the INDEXPROPERTY function.
>|||Sorry, I found a typo - the WHERE clause should be:
where INDEXPROPERTY([id], [name], 'IsStatistics') = 0
Mike Hodgson wrote:
> OK then, how about (not tested):
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
> order by object_name([id]), indid
> If you want it for all user DBs you can wrap it in a cursor like:
> declare @.cmd nvarchar(4000)
> declare @.DBName sysname
> declare AllUserDBs cursor for
> select [name] from master.dbo.sysdatabases
> where [name] not in ('master', 'msdb', 'model', 'tempdb',
> 'distribution', 'pubs', 'Northwind')
> order by [name]
> open AllUserDBs
> fetch next from AllUserDBs into @.DBName
> while (@.@.FETCH_STATUS != -1)
> begin
> if (@.@.FETCH_STATUS != -2)
> begin
> select @.cmd => 'use [@.DB]; ' +
> ' select @.DB as [DB], object_name([id]), indid, [name]
> from dbo.sysindexes ' +
> ' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
> ' order by object_name([id]), indid'
> exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
> end
> fetch next from AllUserDBs into @.DBName
> end
> close AllUserDBs
> deallocate AllUserDBs
> (Please pardon any typos - this is untested and off the top of my head.)
> Cheers,
> Mike.
> Tibor Karaszi wrote:
>> Also, thinks like statistics and hypothetical indexes need to be
>> filtered out. Check out the INDEXPROPERTY function.
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Thursday, March 29, 2012
Finding Indexes
Hi,
Does anyone have a script for or know the easiest way to list the indexes in
all tables (for all user databases) in sql server 2000?
Thanks,
Andrew
That's easy. The easiest way to list all indexes for all tables in a DB is:
select * from MyDB.dbo.sysindexes
Of course, it's not very user friendly - you might want to run it from
the actual DB and throw in some functions like object_name([id]). For
example,
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
order by object_name([id]), indid
For more info see the BOL entries on the sysindexes system table. For
instance, indid 0 for a table is the data (ie. there is no clustered
index for that table - it's a heap), index 1 is the clustered index, 2
to 254 are nonclustered indexes, 255 is for text/image data, etc.
Anyway, check out BOL.
Cheers,
Mike.
Andrew wrote:
> Hi,
> Does anyone have a script for or know the easiest way to list the indexes in
> all tables (for all user databases) in sql server 2000?
> Thanks,
> Andrew
>
|||Also, thinks like statistics and hypothetical indexes need to be filtered out. Check out the
INDEXPROPERTY function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:%23zIgFhy5EHA.3648@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> That's easy. The easiest way to list all indexes for all tables in a DB is:
> select * from MyDB.dbo.sysindexes
> Of course, it's not very user friendly - you might want to run it from the actual DB and throw in
> some functions like object_name([id]). For example,
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> order by object_name([id]), indid
> For more info see the BOL entries on the sysindexes system table. For instance, indid 0 for a
> table is the data (ie. there is no clustered index for that table - it's a heap), index 1 is the
> clustered index, 2 to 254 are nonclustered indexes, 255 is for text/image data, etc. Anyway, check
> out BOL.
> Cheers,
> Mike.
> Andrew wrote:
|||OK then, how about (not tested):
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
order by object_name([id]), indid
If you want it for all user DBs you can wrap it in a cursor like:
declare @.cmd nvarchar(4000)
declare @.DBName sysname
declare AllUserDBs cursor for
select [name] from master.dbo.sysdatabases
where [name] not in ('master', 'msdb', 'model', 'tempdb',
'distribution', 'pubs', 'Northwind')
order by [name]
open AllUserDBs
fetch next from AllUserDBs into @.DBName
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd =
'use [@.DB]; ' +
'select @.DB as [DB], object_name([id]), indid, [name] from
dbo.sysindexes ' +
'where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
'order by object_name([id]), indid'
exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
end
fetch next from AllUserDBs into @.DBName
end
close AllUserDBs
deallocate AllUserDBs
(Please pardon any typos - this is untested and off the top of my head.)
Cheers,
Mike.
Tibor Karaszi wrote:
> Also, thinks like statistics and hypothetical indexes need to be
> filtered out. Check out the INDEXPROPERTY function.
>
|||Sorry, I found a typo - the WHERE clause should be:
where INDEXPROPERTY([id], [name], 'IsStatistics') = 0
Mike Hodgson wrote:[vbcol=seagreen]
> OK then, how about (not tested):
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
> order by object_name([id]), indid
> If you want it for all user DBs you can wrap it in a cursor like:
> declare @.cmd nvarchar(4000)
> declare @.DBName sysname
> declare AllUserDBs cursor for
> select [name] from master.dbo.sysdatabases
> where [name] not in ('master', 'msdb', 'model', 'tempdb',
> 'distribution', 'pubs', 'Northwind')
> order by [name]
> open AllUserDBs
> fetch next from AllUserDBs into @.DBName
> while (@.@.FETCH_STATUS != -1)
> begin
> if (@.@.FETCH_STATUS != -2)
> begin
> select @.cmd =
> 'use [@.DB]; ' +
> ' select @.DB as [DB], object_name([id]), indid, [name]
> from dbo.sysindexes ' +
> ' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
> ' order by object_name([id]), indid'
> exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
> end
> fetch next from AllUserDBs into @.DBName
> end
> close AllUserDBs
> deallocate AllUserDBs
> (Please pardon any typos - this is untested and off the top of my head.)
> Cheers,
> Mike.
> Tibor Karaszi wrote:
Does anyone have a script for or know the easiest way to list the indexes in
all tables (for all user databases) in sql server 2000?
Thanks,
Andrew
That's easy. The easiest way to list all indexes for all tables in a DB is:
select * from MyDB.dbo.sysindexes
Of course, it's not very user friendly - you might want to run it from
the actual DB and throw in some functions like object_name([id]). For
example,
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
order by object_name([id]), indid
For more info see the BOL entries on the sysindexes system table. For
instance, indid 0 for a table is the data (ie. there is no clustered
index for that table - it's a heap), index 1 is the clustered index, 2
to 254 are nonclustered indexes, 255 is for text/image data, etc.
Anyway, check out BOL.
Cheers,
Mike.
Andrew wrote:
> Hi,
> Does anyone have a script for or know the easiest way to list the indexes in
> all tables (for all user databases) in sql server 2000?
> Thanks,
> Andrew
>
|||Also, thinks like statistics and hypothetical indexes need to be filtered out. Check out the
INDEXPROPERTY function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:%23zIgFhy5EHA.3648@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> That's easy. The easiest way to list all indexes for all tables in a DB is:
> select * from MyDB.dbo.sysindexes
> Of course, it's not very user friendly - you might want to run it from the actual DB and throw in
> some functions like object_name([id]). For example,
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> order by object_name([id]), indid
> For more info see the BOL entries on the sysindexes system table. For instance, indid 0 for a
> table is the data (ie. there is no clustered index for that table - it's a heap), index 1 is the
> clustered index, 2 to 254 are nonclustered indexes, 255 is for text/image data, etc. Anyway, check
> out BOL.
> Cheers,
> Mike.
> Andrew wrote:
|||OK then, how about (not tested):
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
order by object_name([id]), indid
If you want it for all user DBs you can wrap it in a cursor like:
declare @.cmd nvarchar(4000)
declare @.DBName sysname
declare AllUserDBs cursor for
select [name] from master.dbo.sysdatabases
where [name] not in ('master', 'msdb', 'model', 'tempdb',
'distribution', 'pubs', 'Northwind')
order by [name]
open AllUserDBs
fetch next from AllUserDBs into @.DBName
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd =
'use [@.DB]; ' +
'select @.DB as [DB], object_name([id]), indid, [name] from
dbo.sysindexes ' +
'where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
'order by object_name([id]), indid'
exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
end
fetch next from AllUserDBs into @.DBName
end
close AllUserDBs
deallocate AllUserDBs
(Please pardon any typos - this is untested and off the top of my head.)
Cheers,
Mike.
Tibor Karaszi wrote:
> Also, thinks like statistics and hypothetical indexes need to be
> filtered out. Check out the INDEXPROPERTY function.
>
|||Sorry, I found a typo - the WHERE clause should be:
where INDEXPROPERTY([id], [name], 'IsStatistics') = 0
Mike Hodgson wrote:[vbcol=seagreen]
> OK then, how about (not tested):
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
> order by object_name([id]), indid
> If you want it for all user DBs you can wrap it in a cursor like:
> declare @.cmd nvarchar(4000)
> declare @.DBName sysname
> declare AllUserDBs cursor for
> select [name] from master.dbo.sysdatabases
> where [name] not in ('master', 'msdb', 'model', 'tempdb',
> 'distribution', 'pubs', 'Northwind')
> order by [name]
> open AllUserDBs
> fetch next from AllUserDBs into @.DBName
> while (@.@.FETCH_STATUS != -1)
> begin
> if (@.@.FETCH_STATUS != -2)
> begin
> select @.cmd =
> 'use [@.DB]; ' +
> ' select @.DB as [DB], object_name([id]), indid, [name]
> from dbo.sysindexes ' +
> ' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
> ' order by object_name([id]), indid'
> exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
> end
> fetch next from AllUserDBs into @.DBName
> end
> close AllUserDBs
> deallocate AllUserDBs
> (Please pardon any typos - this is untested and off the top of my head.)
> Cheers,
> Mike.
> Tibor Karaszi wrote:
Wednesday, March 28, 2012
Finding database size in SQL Express
Our system guy wants me to find out just exactly how much room is being taken up by the SQL Express databases (I am using two). I am not sure how to do this. Is there any way to find the size, or, lacking that, how many records overall? We will probably need to move up to SQL 2005 soon, but he needs to figure out the memory requirements, etc.
Thanks,
Michael
try select [size] from sys.database_files
This will give you a row for each primary datafile and log, to get it all in one statement you could use
select Sum([size]) as total_file_size from sys.database_files
|||Thanks, that worked.
Michael
Finding database on virtual servers
I have a sql 2000 cluster (multiple instance) and I'm going to move my
databases over and spread them out over each instance. Now when my web
servers need to read/write info to the databases how will they find which
virtual server has the database it needs. Do I have to build a connection
string that says, first look here and if not then look here. I be grateful
for any ideas or suggestions.C, your cluster, is it active/active and your have
multiple SQL SERVER instances that you run concurrently
on BOTH boxes, active/active? or is only one node active
and the other is sitting there just in case
(active/passive)? I'm not clear on what you mean by
multiple instances? SQL instances I guess... Either
way, you'd have a virtual name that your application
would refer to. When the node failover, you still call
the virtual name and the cluster wil get you to the
active side of the cluster. Maybe I'm not getting your
question completly?!? Bruce
>--Original Message--
>I have a sql 2000 cluster (multiple instance) and I'm
going to move my
>databases over and spread them out over each instance.
Now when my web
>servers need to read/write info to the databases how
will they find which
>virtual server has the database it needs. Do I have to
build a connection
>string that says, first look here and if not then look
here. I be grateful
>for any ideas or suggestions.
>.
>|||This is an active/active cluster. All instances of sql need to be running at
all times. I am hoping to spread the load (databases) evenly across the
servers. I have 150 databases on various sizes and transaction amounts, plus
a couple on control databases. I don't want to have the control databases in
one instance and the rest in another, because I will not be making efficient
use of my hardware.
"Bruce de Freitas" wrote:
> C, your cluster, is it active/active and your have
> multiple SQL SERVER instances that you run concurrently
> on BOTH boxes, active/active? or is only one node active
> and the other is sitting there just in case
> (active/passive)? I'm not clear on what you mean by
> multiple instances? SQL instances I guess... Either
> way, you'd have a virtual name that your application
> would refer to. When the node failover, you still call
> the virtual name and the cluster wil get you to the
> active side of the cluster. Maybe I'm not getting your
> question completly?!? Bruce
>
>
> >--Original Message--
> >I have a sql 2000 cluster (multiple instance) and I'm
> going to move my
> >databases over and spread them out over each instance.
> Now when my web
> >servers need to read/write info to the databases how
> will they find which
> >virtual server has the database it needs. Do I have to
> build a connection
> >string that says, first look here and if not then look
> here. I be grateful
> >for any ideas or suggestions.
> >
> >.
> >
>|||ok cool, active/active with multiple SQL instances...
I've done more active/passive with single instances, much
less complex then what you're trying to do. But, I
believe it works like this. Say you have two nodes and
you want to have 3 SQL instances active on NodeA (SQL1,
SQL2, SQL3) and 2 other SQL instances active on NodeB
(SQL4, SQL5). While you could play a game with switching
SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
it's much cleaner to have 5 instances on each node, so
the 2 on NodeA sit there as a failover for the 2 on NodeB
and 3 on NodeB sit there as failover for the 3 on NodeA.
You'd have 5 virtual names that your app refers to, but
10 SQL instances across the 2 boxes. If NodeB totally
crashed, then all 5 SQL instances on NodeA would be
active, until NodeB came back online. If you're trying
to do somethign along those lines, then I think you're ok
referring to each virtual name, and let the Cluster
Manager worry about directing SQL requests to the right
place. Bruce
>--Original Message--
>This is an active/active cluster. All instances of sql
need to be running at
>all times. I am hoping to spread the load (databases)
evenly across the
>servers. I have 150 databases on various sizes and
transaction amounts, plus
>a couple on control databases. I don't want to have the
control databases in
>one instance and the rest in another, because I will not
be making efficient
>use of my hardware.
>"Bruce de Freitas" wrote:
>> C, your cluster, is it active/active and your have
>> multiple SQL SERVER instances that you run
concurrently
>> on BOTH boxes, active/active? or is only one node
active
>> and the other is sitting there just in case
>> (active/passive)? I'm not clear on what you mean by
>> multiple instances? SQL instances I guess... Either
>> way, you'd have a virtual name that your application
>> would refer to. When the node failover, you still
call
>> the virtual name and the cluster wil get you to the
>> active side of the cluster. Maybe I'm not getting
your
>> question completly?!? Bruce
>>
>>
>> >--Original Message--
>> >I have a sql 2000 cluster (multiple instance) and I'm
>> going to move my
>> >databases over and spread them out over each
instance.
>> Now when my web
>> >servers need to read/write info to the databases how
>> will they find which
>> >virtual server has the database it needs. Do I have
to
>> build a connection
>> >string that says, first look here and if not then
look
>> here. I be grateful
>> >for any ideas or suggestions.
>> >
>> >.
>> >
>.
>|||You don't understand my question. The fail-over I'm not worried about and we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:
> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> >--Original Message--
> >This is an active/active cluster. All instances of sql
> need to be running at
> >all times. I am hoping to spread the load (databases)
> evenly across the
> >servers. I have 150 databases on various sizes and
> transaction amounts, plus
> >a couple on control databases. I don't want to have the
> control databases in
> >one instance and the rest in another, because I will not
> be making efficient
> >use of my hardware.
> >
> >"Bruce de Freitas" wrote:
> >
> >> C, your cluster, is it active/active and your have
> >> multiple SQL SERVER instances that you run
> concurrently
> >> on BOTH boxes, active/active? or is only one node
> active
> >> and the other is sitting there just in case
> >> (active/passive)? I'm not clear on what you mean by
> >> multiple instances? SQL instances I guess... Either
> >> way, you'd have a virtual name that your application
> >> would refer to. When the node failover, you still
> call
> >> the virtual name and the cluster wil get you to the
> >> active side of the cluster. Maybe I'm not getting
> your
> >> question completly?!? Bruce
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I have a sql 2000 cluster (multiple instance) and I'm
> >> going to move my
> >> >databases over and spread them out over each
> instance.
> >> Now when my web
> >> >servers need to read/write info to the databases how
> >> will they find which
> >> >virtual server has the database it needs. Do I have
> to
> >> build a connection
> >> >string that says, first look here and if not then
> look
> >> here. I be grateful
> >> >for any ideas or suggestions.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>|||For the sake of simplicity, let's say you only have 2 virtual SS instances,
in an Active/Active configuration. Regardless of the current state of the
fail-over configuration--that is, whether or not the current host of the
virtual servers is the same or distributed across the physical nodes--you
would still need to have a connection to each virtual server.
Now, it would be possible to create a third SS virtual instance and make
this sort of the master, virtual server. If you created a control database
for your web server to always connect to, then you could establish linked
servers from this host to the other two. Then, if you always queried this
third server using four-part names, then you could establish queries from
the third sever but against either of the first two.
Also, on the third server, in your control database, you could create the
following VIEW:
CREATE dbo.VIEW ServerDatabases
AS
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER1.master.INFORMATION_SCHEMA.SCHEMATA
UNION ALL
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER2.master.INFORMATION_SCHEMA.SCHEMATA
GO
Then you could query this for a particular database to determine the server.
Then you would execute your query against SERVER3 but using the four-part
name against either SERVER1 or SERVER2 depending on where the database
lived.
SELECT SERVER_NAME
FROM SERVER3.controldatabase.dbo.ServerDatabases
WHERE DATABASE_NAME = 'MyDatabase'
Now, you do not necessarily need the third instance and could use either of
the first two to create this common control database. However, keep in mind
that if you run all of your queries through a common server, that one will
always be used for resources, even if the subsequent call is redirected to
either of the other two servers.
It is more complex, but you would be better off setting up a seperate
connection for each host you query.
Sincerely,
Anthony Thomas
--
"C-Ya" <CYa@.discussions.microsoft.com> wrote in message
news:8B94987E-51F0-4219-A9D4-DE351A9BD098@.microsoft.com...
You don't understand my question. The fail-over I'm not worried about and
we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:
> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> >--Original Message--
> >This is an active/active cluster. All instances of sql
> need to be running at
> >all times. I am hoping to spread the load (databases)
> evenly across the
> >servers. I have 150 databases on various sizes and
> transaction amounts, plus
> >a couple on control databases. I don't want to have the
> control databases in
> >one instance and the rest in another, because I will not
> be making efficient
> >use of my hardware.
> >
> >"Bruce de Freitas" wrote:
> >
> >> C, your cluster, is it active/active and your have
> >> multiple SQL SERVER instances that you run
> concurrently
> >> on BOTH boxes, active/active? or is only one node
> active
> >> and the other is sitting there just in case
> >> (active/passive)? I'm not clear on what you mean by
> >> multiple instances? SQL instances I guess... Either
> >> way, you'd have a virtual name that your application
> >> would refer to. When the node failover, you still
> call
> >> the virtual name and the cluster wil get you to the
> >> active side of the cluster. Maybe I'm not getting
> your
> >> question completly?!? Bruce
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I have a sql 2000 cluster (multiple instance) and I'm
> >> going to move my
> >> >databases over and spread them out over each
> instance.
> >> Now when my web
> >> >servers need to read/write info to the databases how
> >> will they find which
> >> >virtual server has the database it needs. Do I have
> to
> >> build a connection
> >> >string that says, first look here and if not then
> look
> >> here. I be grateful
> >> >for any ideas or suggestions.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>
databases over and spread them out over each instance. Now when my web
servers need to read/write info to the databases how will they find which
virtual server has the database it needs. Do I have to build a connection
string that says, first look here and if not then look here. I be grateful
for any ideas or suggestions.C, your cluster, is it active/active and your have
multiple SQL SERVER instances that you run concurrently
on BOTH boxes, active/active? or is only one node active
and the other is sitting there just in case
(active/passive)? I'm not clear on what you mean by
multiple instances? SQL instances I guess... Either
way, you'd have a virtual name that your application
would refer to. When the node failover, you still call
the virtual name and the cluster wil get you to the
active side of the cluster. Maybe I'm not getting your
question completly?!? Bruce
>--Original Message--
>I have a sql 2000 cluster (multiple instance) and I'm
going to move my
>databases over and spread them out over each instance.
Now when my web
>servers need to read/write info to the databases how
will they find which
>virtual server has the database it needs. Do I have to
build a connection
>string that says, first look here and if not then look
here. I be grateful
>for any ideas or suggestions.
>.
>|||This is an active/active cluster. All instances of sql need to be running at
all times. I am hoping to spread the load (databases) evenly across the
servers. I have 150 databases on various sizes and transaction amounts, plus
a couple on control databases. I don't want to have the control databases in
one instance and the rest in another, because I will not be making efficient
use of my hardware.
"Bruce de Freitas" wrote:
> C, your cluster, is it active/active and your have
> multiple SQL SERVER instances that you run concurrently
> on BOTH boxes, active/active? or is only one node active
> and the other is sitting there just in case
> (active/passive)? I'm not clear on what you mean by
> multiple instances? SQL instances I guess... Either
> way, you'd have a virtual name that your application
> would refer to. When the node failover, you still call
> the virtual name and the cluster wil get you to the
> active side of the cluster. Maybe I'm not getting your
> question completly?!? Bruce
>
>
> >--Original Message--
> >I have a sql 2000 cluster (multiple instance) and I'm
> going to move my
> >databases over and spread them out over each instance.
> Now when my web
> >servers need to read/write info to the databases how
> will they find which
> >virtual server has the database it needs. Do I have to
> build a connection
> >string that says, first look here and if not then look
> here. I be grateful
> >for any ideas or suggestions.
> >
> >.
> >
>|||ok cool, active/active with multiple SQL instances...
I've done more active/passive with single instances, much
less complex then what you're trying to do. But, I
believe it works like this. Say you have two nodes and
you want to have 3 SQL instances active on NodeA (SQL1,
SQL2, SQL3) and 2 other SQL instances active on NodeB
(SQL4, SQL5). While you could play a game with switching
SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
it's much cleaner to have 5 instances on each node, so
the 2 on NodeA sit there as a failover for the 2 on NodeB
and 3 on NodeB sit there as failover for the 3 on NodeA.
You'd have 5 virtual names that your app refers to, but
10 SQL instances across the 2 boxes. If NodeB totally
crashed, then all 5 SQL instances on NodeA would be
active, until NodeB came back online. If you're trying
to do somethign along those lines, then I think you're ok
referring to each virtual name, and let the Cluster
Manager worry about directing SQL requests to the right
place. Bruce
>--Original Message--
>This is an active/active cluster. All instances of sql
need to be running at
>all times. I am hoping to spread the load (databases)
evenly across the
>servers. I have 150 databases on various sizes and
transaction amounts, plus
>a couple on control databases. I don't want to have the
control databases in
>one instance and the rest in another, because I will not
be making efficient
>use of my hardware.
>"Bruce de Freitas" wrote:
>> C, your cluster, is it active/active and your have
>> multiple SQL SERVER instances that you run
concurrently
>> on BOTH boxes, active/active? or is only one node
active
>> and the other is sitting there just in case
>> (active/passive)? I'm not clear on what you mean by
>> multiple instances? SQL instances I guess... Either
>> way, you'd have a virtual name that your application
>> would refer to. When the node failover, you still
call
>> the virtual name and the cluster wil get you to the
>> active side of the cluster. Maybe I'm not getting
your
>> question completly?!? Bruce
>>
>>
>> >--Original Message--
>> >I have a sql 2000 cluster (multiple instance) and I'm
>> going to move my
>> >databases over and spread them out over each
instance.
>> Now when my web
>> >servers need to read/write info to the databases how
>> will they find which
>> >virtual server has the database it needs. Do I have
to
>> build a connection
>> >string that says, first look here and if not then
look
>> here. I be grateful
>> >for any ideas or suggestions.
>> >
>> >.
>> >
>.
>|||You don't understand my question. The fail-over I'm not worried about and we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:
> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> >--Original Message--
> >This is an active/active cluster. All instances of sql
> need to be running at
> >all times. I am hoping to spread the load (databases)
> evenly across the
> >servers. I have 150 databases on various sizes and
> transaction amounts, plus
> >a couple on control databases. I don't want to have the
> control databases in
> >one instance and the rest in another, because I will not
> be making efficient
> >use of my hardware.
> >
> >"Bruce de Freitas" wrote:
> >
> >> C, your cluster, is it active/active and your have
> >> multiple SQL SERVER instances that you run
> concurrently
> >> on BOTH boxes, active/active? or is only one node
> active
> >> and the other is sitting there just in case
> >> (active/passive)? I'm not clear on what you mean by
> >> multiple instances? SQL instances I guess... Either
> >> way, you'd have a virtual name that your application
> >> would refer to. When the node failover, you still
> call
> >> the virtual name and the cluster wil get you to the
> >> active side of the cluster. Maybe I'm not getting
> your
> >> question completly?!? Bruce
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I have a sql 2000 cluster (multiple instance) and I'm
> >> going to move my
> >> >databases over and spread them out over each
> instance.
> >> Now when my web
> >> >servers need to read/write info to the databases how
> >> will they find which
> >> >virtual server has the database it needs. Do I have
> to
> >> build a connection
> >> >string that says, first look here and if not then
> look
> >> here. I be grateful
> >> >for any ideas or suggestions.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>|||For the sake of simplicity, let's say you only have 2 virtual SS instances,
in an Active/Active configuration. Regardless of the current state of the
fail-over configuration--that is, whether or not the current host of the
virtual servers is the same or distributed across the physical nodes--you
would still need to have a connection to each virtual server.
Now, it would be possible to create a third SS virtual instance and make
this sort of the master, virtual server. If you created a control database
for your web server to always connect to, then you could establish linked
servers from this host to the other two. Then, if you always queried this
third server using four-part names, then you could establish queries from
the third sever but against either of the first two.
Also, on the third server, in your control database, you could create the
following VIEW:
CREATE dbo.VIEW ServerDatabases
AS
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER1.master.INFORMATION_SCHEMA.SCHEMATA
UNION ALL
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER2.master.INFORMATION_SCHEMA.SCHEMATA
GO
Then you could query this for a particular database to determine the server.
Then you would execute your query against SERVER3 but using the four-part
name against either SERVER1 or SERVER2 depending on where the database
lived.
SELECT SERVER_NAME
FROM SERVER3.controldatabase.dbo.ServerDatabases
WHERE DATABASE_NAME = 'MyDatabase'
Now, you do not necessarily need the third instance and could use either of
the first two to create this common control database. However, keep in mind
that if you run all of your queries through a common server, that one will
always be used for resources, even if the subsequent call is redirected to
either of the other two servers.
It is more complex, but you would be better off setting up a seperate
connection for each host you query.
Sincerely,
Anthony Thomas
--
"C-Ya" <CYa@.discussions.microsoft.com> wrote in message
news:8B94987E-51F0-4219-A9D4-DE351A9BD098@.microsoft.com...
You don't understand my question. The fail-over I'm not worried about and
we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:
> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> >--Original Message--
> >This is an active/active cluster. All instances of sql
> need to be running at
> >all times. I am hoping to spread the load (databases)
> evenly across the
> >servers. I have 150 databases on various sizes and
> transaction amounts, plus
> >a couple on control databases. I don't want to have the
> control databases in
> >one instance and the rest in another, because I will not
> be making efficient
> >use of my hardware.
> >
> >"Bruce de Freitas" wrote:
> >
> >> C, your cluster, is it active/active and your have
> >> multiple SQL SERVER instances that you run
> concurrently
> >> on BOTH boxes, active/active? or is only one node
> active
> >> and the other is sitting there just in case
> >> (active/passive)? I'm not clear on what you mean by
> >> multiple instances? SQL instances I guess... Either
> >> way, you'd have a virtual name that your application
> >> would refer to. When the node failover, you still
> call
> >> the virtual name and the cluster wil get you to the
> >> active side of the cluster. Maybe I'm not getting
> your
> >> question completly?!? Bruce
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I have a sql 2000 cluster (multiple instance) and I'm
> >> going to move my
> >> >databases over and spread them out over each
> instance.
> >> Now when my web
> >> >servers need to read/write info to the databases how
> >> will they find which
> >> >virtual server has the database it needs. Do I have
> to
> >> build a connection
> >> >string that says, first look here and if not then
> look
> >> here. I be grateful
> >> >for any ideas or suggestions.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>
Finding CPU usage by database on a server
We often have many databases on a single SQL Server instance. Is there a
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advanceKevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advanceKevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>
Finding CPU usage by database on a server
We often have many databases on a single SQL Server instance. Is there a
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advance
Kevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com
|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance
|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest
.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advance
Kevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com
|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance
|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest
.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>
Monday, March 26, 2012
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.
sqlFinding 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.
Wednesday, March 21, 2012
Find unused databases on the server
Hi,
I have to find all databases on the server that haven't been used for last 3
month.
Is there an easy way to get this list ?
Programmer
This is hard to ascertain. Personally, I've set up a job to poll
the sysprocesses virtual table and to log into an audit table
I've built the database name and whether anyone has connected.
Someone else in another thread mentioned setting up autoclose
on a database and checking the SQL Server error log for when
the db opens. I'm little mixed on that because any queries issued
to the database will lag as the database is mounted.
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer
|||Backup first
Then delete the db's and wait to see who complains
Jeff
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer
|||A bit less drastic then the previous post, you could always put the db(s) in
single user mode. You could even put the autoclose options to those DBs and
take a look at your logs (keep in mind that the DBs will open whenever you
back them up or browse the list of DBs in EM) but you might have an idea if
the dbs are used or not.
Sasan Saidi
"Jeff Dillon" wrote:
> Backup first
> Then delete the db's and wait to see who complains
> Jeff
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> 3
>
>
|||You could create an autostart proc that begins a trace to do limited small
audit tracing. Capture just Audit Login and Audit Logout events and note the
dbid connected to. Spool these to a table.
Another, if you are regularly backing up the databases, you could examine
the msdb.dbo.backupset table and note the LSN increase between successive
backups. If the values are small, then there is not much activity going on
in the system. The LSN values are updated whenever transactions occur to the
database. No transactions, very little increase in LSN between backups.
Now, there are system processes that do transactions; so, the LSNs will
unlikely be the same. However, they should be much closer together than an
active database would be.
Sincerely,
Anthony Thomas
"Sasan Saidi" wrote:
[vbcol=seagreen]
> A bit less drastic then the previous post, you could always put the db(s) in
> single user mode. You could even put the autoclose options to those DBs and
> take a look at your logs (keep in mind that the DBs will open whenever you
> back them up or browse the list of DBs in EM) but you might have an idea if
> the dbs are used or not.
> Sasan Saidi
> "Jeff Dillon" wrote:
I have to find all databases on the server that haven't been used for last 3
month.
Is there an easy way to get this list ?
Programmer
This is hard to ascertain. Personally, I've set up a job to poll
the sysprocesses virtual table and to log into an audit table
I've built the database name and whether anyone has connected.
Someone else in another thread mentioned setting up autoclose
on a database and checking the SQL Server error log for when
the db opens. I'm little mixed on that because any queries issued
to the database will lag as the database is mounted.
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer
|||Backup first
Then delete the db's and wait to see who complains
Jeff
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer
|||A bit less drastic then the previous post, you could always put the db(s) in
single user mode. You could even put the autoclose options to those DBs and
take a look at your logs (keep in mind that the DBs will open whenever you
back them up or browse the list of DBs in EM) but you might have an idea if
the dbs are used or not.
Sasan Saidi
"Jeff Dillon" wrote:
> Backup first
> Then delete the db's and wait to see who complains
> Jeff
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> 3
>
>
|||You could create an autostart proc that begins a trace to do limited small
audit tracing. Capture just Audit Login and Audit Logout events and note the
dbid connected to. Spool these to a table.
Another, if you are regularly backing up the databases, you could examine
the msdb.dbo.backupset table and note the LSN increase between successive
backups. If the values are small, then there is not much activity going on
in the system. The LSN values are updated whenever transactions occur to the
database. No transactions, very little increase in LSN between backups.
Now, there are system processes that do transactions; so, the LSNs will
unlikely be the same. However, they should be much closer together than an
active database would be.
Sincerely,
Anthony Thomas
"Sasan Saidi" wrote:
[vbcol=seagreen]
> A bit less drastic then the previous post, you could always put the db(s) in
> single user mode. You could even put the autoclose options to those DBs and
> take a look at your logs (keep in mind that the DBs will open whenever you
> back them up or browse the list of DBs in EM) but you might have an idea if
> the dbs are used or not.
> Sasan Saidi
> "Jeff Dillon" wrote:
Find unused databases on the server
Hi,
I have to find all databases on the server that haven't been used for last 3
month.
Is there an easy way to get this list ?
ProgrammerThis is hard to ascertain. Personally, I've set up a job to poll
the sysprocesses virtual table and to log into an audit table
I've built the database name and whether anyone has connected.
Someone else in another thread mentioned setting up autoclose
on a database and checking the SQL Server error log for when
the db opens. I'm little mixed on that because any queries issued
to the database will lag as the database is mounted.
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer|||Backup first
Then delete the db's and wait to see who complains
Jeff
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer|||A bit less drastic then the previous post, you could always put the db(s) in
single user mode. You could even put the autoclose options to those DBs and
take a look at your logs (keep in mind that the DBs will open whenever you
back them up or browse the list of DBs in EM) but you might have an idea if
the dbs are used or not.
Sasan Saidi
"Jeff Dillon" wrote:
> Backup first
> Then delete the db's and wait to see who complains
> Jeff
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> 3
>
>|||You could create an autostart proc that begins a trace to do limited small
audit tracing. Capture just Audit Login and Audit Logout events and note th
e
dbid connected to. Spool these to a table.
Another, if you are regularly backing up the databases, you could examine
the msdb.dbo.backupset table and note the LSN increase between successive
backups. If the values are small, then there is not much activity going on
in the system. The LSN values are updated whenever transactions occur to th
e
database. No transactions, very little increase in LSN between backups.
Now, there are system processes that do transactions; so, the LSNs will
unlikely be the same. However, they should be much closer together than an
active database would be.
Sincerely,
Anthony Thomas
"Sasan Saidi" wrote:
[vbcol=seagreen]
> A bit less drastic then the previous post, you could always put the db(s)
in
> single user mode. You could even put the autoclose options to those DBs an
d
> take a look at your logs (keep in mind that the DBs will open whenever you
> back them up or browse the list of DBs in EM) but you might have an idea i
f
> the dbs are used or not.
> Sasan Saidi
> "Jeff Dillon" wrote:
>
I have to find all databases on the server that haven't been used for last 3
month.
Is there an easy way to get this list ?
ProgrammerThis is hard to ascertain. Personally, I've set up a job to poll
the sysprocesses virtual table and to log into an audit table
I've built the database name and whether anyone has connected.
Someone else in another thread mentioned setting up autoclose
on a database and checking the SQL Server error log for when
the db opens. I'm little mixed on that because any queries issued
to the database will lag as the database is mounted.
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer|||Backup first
Then delete the db's and wait to see who complains
Jeff
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer|||A bit less drastic then the previous post, you could always put the db(s) in
single user mode. You could even put the autoclose options to those DBs and
take a look at your logs (keep in mind that the DBs will open whenever you
back them up or browse the list of DBs in EM) but you might have an idea if
the dbs are used or not.
Sasan Saidi
"Jeff Dillon" wrote:
> Backup first
> Then delete the db's and wait to see who complains
> Jeff
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> 3
>
>|||You could create an autostart proc that begins a trace to do limited small
audit tracing. Capture just Audit Login and Audit Logout events and note th
e
dbid connected to. Spool these to a table.
Another, if you are regularly backing up the databases, you could examine
the msdb.dbo.backupset table and note the LSN increase between successive
backups. If the values are small, then there is not much activity going on
in the system. The LSN values are updated whenever transactions occur to th
e
database. No transactions, very little increase in LSN between backups.
Now, there are system processes that do transactions; so, the LSNs will
unlikely be the same. However, they should be much closer together than an
active database would be.
Sincerely,
Anthony Thomas
"Sasan Saidi" wrote:
[vbcol=seagreen]
> A bit less drastic then the previous post, you could always put the db(s)
in
> single user mode. You could even put the autoclose options to those DBs an
d
> take a look at your logs (keep in mind that the DBs will open whenever you
> back them up or browse the list of DBs in EM) but you might have an idea i
f
> the dbs are used or not.
> Sasan Saidi
> "Jeff Dillon" wrote:
>
Find unused databases on the server
Hi,
I have to find all databases on the server that haven't been used for last 3
month.
Is there an easy way to get this list ?
--
ProgrammerThis is hard to ascertain. Personally, I've set up a job to poll
the sysprocesses virtual table and to log into an audit table
I've built the database name and whether anyone has connected.
Someone else in another thread mentioned setting up autoclose
on a database and checking the SQL Server error log for when
the db opens. I'm little mixed on that because any queries issued
to the database will lag as the database is mounted.
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer|||Backup first
Then delete the db's and wait to see who complains
Jeff
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer|||A bit less drastic then the previous post, you could always put the db(s) in
single user mode. You could even put the autoclose options to those DBs and
take a look at your logs (keep in mind that the DBs will open whenever you
back them up or browse the list of DBs in EM) but you might have an idea if
the dbs are used or not.
Sasan Saidi
"Jeff Dillon" wrote:
> Backup first
> Then delete the db's and wait to see who complains
> Jeff
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> > Hi,
> > I have to find all databases on the server that haven't been used for last
> 3
> > month.
> > Is there an easy way to get this list ?
> >
> > --
> > Programmer
>
>|||You could create an autostart proc that begins a trace to do limited small
audit tracing. Capture just Audit Login and Audit Logout events and note the
dbid connected to. Spool these to a table.
Another, if you are regularly backing up the databases, you could examine
the msdb.dbo.backupset table and note the LSN increase between successive
backups. If the values are small, then there is not much activity going on
in the system. The LSN values are updated whenever transactions occur to the
database. No transactions, very little increase in LSN between backups.
Now, there are system processes that do transactions; so, the LSNs will
unlikely be the same. However, they should be much closer together than an
active database would be.
Sincerely,
Anthony Thomas
"Sasan Saidi" wrote:
> A bit less drastic then the previous post, you could always put the db(s) in
> single user mode. You could even put the autoclose options to those DBs and
> take a look at your logs (keep in mind that the DBs will open whenever you
> back them up or browse the list of DBs in EM) but you might have an idea if
> the dbs are used or not.
> Sasan Saidi
> "Jeff Dillon" wrote:
> > Backup first
> >
> > Then delete the db's and wait to see who complains
> >
> > Jeff
> >
> > "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> > news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> > > Hi,
> > > I have to find all databases on the server that haven't been used for last
> > 3
> > > month.
> > > Is there an easy way to get this list ?
> > >
> > > --
> > > Programmer
> >
> >
> >
I have to find all databases on the server that haven't been used for last 3
month.
Is there an easy way to get this list ?
--
ProgrammerThis is hard to ascertain. Personally, I've set up a job to poll
the sysprocesses virtual table and to log into an audit table
I've built the database name and whether anyone has connected.
Someone else in another thread mentioned setting up autoclose
on a database and checking the SQL Server error log for when
the db opens. I'm little mixed on that because any queries issued
to the database will lag as the database is mounted.
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer|||Backup first
Then delete the db's and wait to see who complains
Jeff
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> Hi,
> I have to find all databases on the server that haven't been used for last
3
> month.
> Is there an easy way to get this list ?
> --
> Programmer|||A bit less drastic then the previous post, you could always put the db(s) in
single user mode. You could even put the autoclose options to those DBs and
take a look at your logs (keep in mind that the DBs will open whenever you
back them up or browse the list of DBs in EM) but you might have an idea if
the dbs are used or not.
Sasan Saidi
"Jeff Dillon" wrote:
> Backup first
> Then delete the db's and wait to see who complains
> Jeff
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> > Hi,
> > I have to find all databases on the server that haven't been used for last
> 3
> > month.
> > Is there an easy way to get this list ?
> >
> > --
> > Programmer
>
>|||You could create an autostart proc that begins a trace to do limited small
audit tracing. Capture just Audit Login and Audit Logout events and note the
dbid connected to. Spool these to a table.
Another, if you are regularly backing up the databases, you could examine
the msdb.dbo.backupset table and note the LSN increase between successive
backups. If the values are small, then there is not much activity going on
in the system. The LSN values are updated whenever transactions occur to the
database. No transactions, very little increase in LSN between backups.
Now, there are system processes that do transactions; so, the LSNs will
unlikely be the same. However, they should be much closer together than an
active database would be.
Sincerely,
Anthony Thomas
"Sasan Saidi" wrote:
> A bit less drastic then the previous post, you could always put the db(s) in
> single user mode. You could even put the autoclose options to those DBs and
> take a look at your logs (keep in mind that the DBs will open whenever you
> back them up or browse the list of DBs in EM) but you might have an idea if
> the dbs are used or not.
> Sasan Saidi
> "Jeff Dillon" wrote:
> > Backup first
> >
> > Then delete the db's and wait to see who complains
> >
> > Jeff
> >
> > "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> > news:04E151E5-D3BB-4689-9168-185C7F116C0F@.microsoft.com...
> > > Hi,
> > > I have to find all databases on the server that haven't been used for last
> > 3
> > > month.
> > > Is there an easy way to get this list ?
> > >
> > > --
> > > Programmer
> >
> >
> >
Monday, March 12, 2012
Find out owner of database for SQL 2000 and 2005 databases
Hello,
As the title says I need to find out the owner of database on both sql 2000 and 2005. I found a query which works great on sql 2005..
As the title says I need to find out the owner of database on both sql 2000 and 2005. I found a query which works great on sql 2005..
select suser_sname(owner_sid) from sys.databases where name = 'DatabaseName'
But cannot run this in QA against a sql 2000 database -- "Invalid object name 'sys.databases'."
Anyone know how I can accomplish this?
Select * from master.dbo.sysdatabases doesn't give me any promising data either,
BI was almost there...for anyone looking to do this you need to change a couple of clauses from the 2005 query i posted. Firstly SELECT *
FROM sys.databases -> SELECT * FROM master.dbo.sysdatabases and the column owner_sid needs to be changed to its 2000 equivalent of just sid...
All in all
2005: select suser_sname(owner_sid) from sys.databases where name = 'DatabaseName'
2000: select suser_sname(sid) from master.dbo.sysdatabases where name = 'DatabaseName'
B
Sunday, February 26, 2012
Find databases in FULL recovery mode
I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.com
Why don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.com
Why don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Find databases in FULL recovery mode
I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Find databases in FULL recovery mode
I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Find all read-only Databases using Stored Procedure
I am running a Stored Procedure that goes through all the databases and
reindexs them. However when it reaches a database that is "read
only" the program quits with an error. If there an easy way to
determine if the database is Read only and skip it if it is?
Using SQL 2000 / 2005
Thanks
-Matt-
DECLARE @.ExecSQLcmd varchar(1024)
DECLARE @.DBNum_to_Name int
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
100'
FROM master.dbo.sysdatabases
WHERE dbid =@.DBNum_to_Name
EXEC (@.ExecSQLcmd)
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
DBs here.
ENDAND DATABASEPROPERTY(name, 'IsReadOnly') = 0
<MKruer@.gmail.com> wrote in message
news:1138305254.208071.14400@.g14g2000cwa.googlegroups.com...
>I am running a Stored Procedure that goes through all the databases and
> reindexs them. However when it reaches a database that is "read
> only" the program quits with an error. If there an easy way to
> determine if the database is Read only and skip it if it is?
> Using SQL 2000 / 2005
> Thanks
> -Matt-
>
> DECLARE @.ExecSQLcmd varchar(1024)
> DECLARE @.DBNum_to_Name int
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4
> WHILE @.DBNum_to_Name is not null
> BEGIN
> SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
> 100'
> FROM master.dbo.sysdatabases
> WHERE dbid =@.DBNum_to_Name
> EXEC (@.ExecSQLcmd)
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > @.DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
> DBs here.
> END
>
reindexs them. However when it reaches a database that is "read
only" the program quits with an error. If there an easy way to
determine if the database is Read only and skip it if it is?
Using SQL 2000 / 2005
Thanks
-Matt-
DECLARE @.ExecSQLcmd varchar(1024)
DECLARE @.DBNum_to_Name int
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
100'
FROM master.dbo.sysdatabases
WHERE dbid =@.DBNum_to_Name
EXEC (@.ExecSQLcmd)
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
DBs here.
ENDAND DATABASEPROPERTY(name, 'IsReadOnly') = 0
<MKruer@.gmail.com> wrote in message
news:1138305254.208071.14400@.g14g2000cwa.googlegroups.com...
>I am running a Stored Procedure that goes through all the databases and
> reindexs them. However when it reaches a database that is "read
> only" the program quits with an error. If there an easy way to
> determine if the database is Read only and skip it if it is?
> Using SQL 2000 / 2005
> Thanks
> -Matt-
>
> DECLARE @.ExecSQLcmd varchar(1024)
> DECLARE @.DBNum_to_Name int
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4
> WHILE @.DBNum_to_Name is not null
> BEGIN
> SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
> 100'
> FROM master.dbo.sysdatabases
> WHERE dbid =@.DBNum_to_Name
> EXEC (@.ExecSQLcmd)
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > @.DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
> DBs here.
> END
>
Friday, February 24, 2012
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.
>
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.
>
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
.
>
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
.
>
Subscribe to:
Posts (Atom)