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 user. Show all posts
Showing posts with label user. 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 date using PATINDEX in varchar
Hi:
One of the columns in my table is a notes column i.e everytime a user
updates it the date,time, users name and his/her note gets appended in
front. The notes column has text in following format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
I am supposed to filter out the data by each user,its date and time i.e
8/4/2006 Linda King Per MDCR no clm on file,reblld.
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
Any suggestions are welcomed.Hi
DECLARE @.st VARCHAR(100)
SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
SELECT
SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
<sfazili@.gmail.com> wrote in message
news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> Hi:
> One of the columns in my table is a notes column i.e everytime a user
> updates it the date,time, users name and his/her note gets appended in
> front. The notes column has text in following format:
> 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> I am supposed to filter out the data by each user,its date and time i.e
> 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> Any suggestions are welcomed.
>|||Hi Uri:
Thanks for ur reply. Actually the notes field has text in the follwoing
format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
Each update to the notes field just get appended to the field in front.
Firstly, I need to separate each individual update..cant use space as
an identifier... need help here. The thing is that after each
indiviaudal note is identified it should be in follwoing format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
After this need to separate each individaul note into thre columns:
8/4/2006 Linda King Per MDCR/ no clm on file,reblld
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22
3/30/2006 Maria James Patient asks to follow -MEDICARE
Any suggestions appreciated
Uri Dimant wrote:
> Hi
> DECLARE @.st VARCHAR(100)
> SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
> SELECT
> SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
> CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
> SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
>
>
> <sfazili@.gmail.com> wrote in message
> news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> > Hi:
> >
> > One of the columns in my table is a notes column i.e everytime a user
> > updates it the date,time, users name and his/her note gets appended in
> > front. The notes column has text in following format:
> >
> > 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> > 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> >
> > I am supposed to filter out the data by each user,its date and time i.e
> > 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> > 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> >
> > Any suggestions are welcomed.
> >
One of the columns in my table is a notes column i.e everytime a user
updates it the date,time, users name and his/her note gets appended in
front. The notes column has text in following format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
I am supposed to filter out the data by each user,its date and time i.e
8/4/2006 Linda King Per MDCR no clm on file,reblld.
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
Any suggestions are welcomed.Hi
DECLARE @.st VARCHAR(100)
SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
SELECT
SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
<sfazili@.gmail.com> wrote in message
news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> Hi:
> One of the columns in my table is a notes column i.e everytime a user
> updates it the date,time, users name and his/her note gets appended in
> front. The notes column has text in following format:
> 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> I am supposed to filter out the data by each user,its date and time i.e
> 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> Any suggestions are welcomed.
>|||Hi Uri:
Thanks for ur reply. Actually the notes field has text in the follwoing
format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
Each update to the notes field just get appended to the field in front.
Firstly, I need to separate each individual update..cant use space as
an identifier... need help here. The thing is that after each
indiviaudal note is identified it should be in follwoing format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
After this need to separate each individaul note into thre columns:
8/4/2006 Linda King Per MDCR/ no clm on file,reblld
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22
3/30/2006 Maria James Patient asks to follow -MEDICARE
Any suggestions appreciated
Uri Dimant wrote:
> Hi
> DECLARE @.st VARCHAR(100)
> SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
> SELECT
> SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
> CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
> SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
>
>
> <sfazili@.gmail.com> wrote in message
> news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> > Hi:
> >
> > One of the columns in my table is a notes column i.e everytime a user
> > updates it the date,time, users name and his/her note gets appended in
> > front. The notes column has text in following format:
> >
> > 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> > 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> >
> > I am supposed to filter out the data by each user,its date and time i.e
> > 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> > 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> >
> > Any suggestions are welcomed.
> >
Monday, March 26, 2012
finding columns
In a specific database. I would like to check to see if a particular column
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and Query Analyzer for example. You can do it programatically via ADO.OpenSchema(), sp_help, or DMO... Of course querying the system tables is always an option -- something like (this doesn't narrow to the database level)
select so.name as 'Table', sc.Name as 'Column
from syscolumns s
join sysobjects so on so.id = sc.i
wher
xo.xtype = 'u
--an
--sc.name like '%column_name_to_find%
group by so.name,sc.nam
...the usual caveats apply (MS does not recommend using the system tables, etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > In a specific database. I would like to check to see if a particular
> column
> > exists in all my tables i.e user created tables. I know I have to use
the
> > sysobjects and syscolumns table. But I am sorta at loss to find out how
> they
> > are related.
> >
> > Please Help
> >
> > VJ
> >
> >
>
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and Query Analyzer for example. You can do it programatically via ADO.OpenSchema(), sp_help, or DMO... Of course querying the system tables is always an option -- something like (this doesn't narrow to the database level)
select so.name as 'Table', sc.Name as 'Column
from syscolumns s
join sysobjects so on so.id = sc.i
wher
xo.xtype = 'u
--an
--sc.name like '%column_name_to_find%
group by so.name,sc.nam
...the usual caveats apply (MS does not recommend using the system tables, etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > In a specific database. I would like to check to see if a particular
> column
> > exists in all my tables i.e user created tables. I know I have to use
the
> > sysobjects and syscolumns table. But I am sorta at loss to find out how
> they
> > are related.
> >
> > Please Help
> >
> > VJ
> >
> >
>
finding columns
In a specific database. I would like to check to see if a particular column
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and
Query Analyzer for example. You can do it programatically via ADO.OpenSche
ma(), sp_help, or DMO... Of course querying the system tables is always an
option -- something like (t
his doesn't narrow to the database level):
select so.name as 'Table', sc.Name as 'Column'
from syscolumns sc
join sysobjects so on so.id = sc.id
where
xo.xtype = 'u'
--and
--sc.name like '%column_name_to_find%'
group by so.name,sc.name
...the usual caveats apply (MS does not recommend using the system tables,
etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> column
the
> they
>
exists in all my tables i.e user created tables. I know I have to use the
sysobjects and syscolumns table. But I am sorta at loss to find out how they
are related.
Please Help
VJin QA hit F4 then search. It is faster.
"VJ" <vijaybalki@.yahoo.com> wrote in message
news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> In a specific database. I would like to check to see if a particular
column
> exists in all my tables i.e user created tables. I know I have to use the
> sysobjects and syscolumns table. But I am sorta at loss to find out how
they
> are related.
> Please Help
> VJ
>|||Some query tools can do this, urSQL (http://www.urbanresearch.com/ursql) and
Query Analyzer for example. You can do it programatically via ADO.OpenSche
ma(), sp_help, or DMO... Of course querying the system tables is always an
option -- something like (t
his doesn't narrow to the database level):
select so.name as 'Table', sc.Name as 'Column'
from syscolumns sc
join sysobjects so on so.id = sc.id
where
xo.xtype = 'u'
--and
--sc.name like '%column_name_to_find%'
group by so.name,sc.name
...the usual caveats apply (MS does not recommend using the system tables,
etc, etc)|||Thanks for the input
"ME" <mail@.moon.net> wrote in message
news:evDDMvP8DHA.2028@.TK2MSFTNGP10.phx.gbl...
> in QA hit F4 then search. It is faster.
> "VJ" <vijaybalki@.yahoo.com> wrote in message
> news:uMIaRtP8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> column
the
> they
>
Finding a users is adult or minor using a simple select query
my problem is that i need to find if a user is adult or minor using a
simple select query .I have the date of birth of the user and a column
named isadult as well in the table but the isadult column also updates
only when the user enters or updates his/her details.
I even tried using a sql function bt it wont allow me to use getdate
function.
simple select query .I have the date of birth of the user and a column
named isadult as well in the table but the isadult column also updates
only when the user enters or updates his/her details.
I even tried using a sql function bt it wont allow me to use getdate
function.
Any solution are welcome unless it makes me fetch records using a
select query in a stored procedure.
Thanx in AdvanceUdit Handa wrote:
> my problem is that i need to find if a user is adult or minor using a
> simple select query .I have the date of birth of the user and a
column
> named isadult as well in the table but the isadult column also
updates
> only when the user enters or updates his/her details.
> I even tried using a sql function bt it wont allow me to use getdate
> function.
Can you post the exact syntax for the SQL function you used?|||On 23 Mar 2005 03:58:56 -0800, Udit Handa wrote:
>my problem is that i need to find if a user is adult or minor using a
>simple select query .I have the date of birth of the user and a column
>named isadult as well in the table but the isadult column also updates
>only when the user enters or updates his/her details.
>I even tried using a sql function bt it wont allow me to use getdate
>function.
>Any solution are welcome unless it makes me fetch records using a
>select query in a stored procedure.
>Thanx in Advance
Hi Udit,
The best solution is to remove the IsAdult column, drop the function and
create a view instead:
CREATE VIEW MyView
AS
SELECT Col1, Col2, ..., ColN, BirthDate,
CASE WHEN BirthDate > DATEADD (year, -18, CURRENT_TIMESTAMP)
THEN 'Underage'
ELSE 'Adult'
END AS IsAdult
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> I need to find if a user is adult or minor using a simple select
query .I have the date of birth of the user and a column named isadult
as well in the table but the isadult column also updates only when the
user enters or updates his/her details. <<
Now you have learned one of the many reasons that we do not use flags
in SQL, like we did in punch card systems. Drop the "isadult" flag; it
is a redundant computed column and create a VIEW that uses the date of
birth with the CURRENT-TIMESTAMP to determine this fact.
I also hope that you are not using the proprietary, low-level BIT data
type for this incorrect design.
Finding a user that has multiple log-ins at the same time
I have a table that contains the following
UserName - Which is unique to each user
TimeStart - Which is the time and date that the user logs in
TimeEnd - Which is the time and date that the user logs out
Using SQL, how do you find who has logged in using the same account within the same time period. In other words, Two people are using the same username and are logged in at the same time.
Try this query....
Select
MainQ.*
From
UserLog MainQ
Join
(
Select
Main.UserName,
Main.TimeStart,
Main.TimeEnd
From
UserLog Main
Join UserLog Started On Main.UserName = Started.UserName
And Main.TimeStart <= Started.TimeStart
And Main.TimeEnd >= Started.TimeStart
Group BY
Main.UserName,
Main.TimeStart,
Main.TimeEnd
Having Count(*) <> 1
) as Data
On MainQ.UserName = Data.UserName
And MainQ.TimeStart >= Data.TimeStart
And MainQ.TimeStart <= Data.TimeEnd
Friday, March 23, 2012
finding a string somewhere in the MS SQL user tables
Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
MichaelUse the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael|||If you now the column name you can do a search with object browser in query
analyzer on the database.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"fauxDBA@.gmail.com" wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
>
>|||On 4 May, 13:47, faux...@.gmail.com wrote:[vbcol=seagreen]
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
>
>
>
>
thanks I will try that|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> If you now the column name you can do a search with object browser in quer
y
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
> "faux...@.gmail.com" wrote:
>
>
>
>
>
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegroups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
----
---
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
----
---|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ----
---
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ----
----
thanks I will try that
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
MichaelUse the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael|||If you now the column name you can do a search with object browser in query
analyzer on the database.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"fauxDBA@.gmail.com" wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
>
>|||On 4 May, 13:47, faux...@.gmail.com wrote:[vbcol=seagreen]
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
>
>
>
>
thanks I will try that|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> If you now the column name you can do a search with object browser in quer
y
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
> "faux...@.gmail.com" wrote:
>
>
>
>
>
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegroups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
----
---
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
----
---|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ----
---
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ----
----
thanks I will try that
finding a string somewhere in the MS SQL user tables
Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
Michael
Use the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
|||If you now the column name you can do a search with object browser in query
analyzer on the database.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"fauxDBA@.gmail.com" wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
>
>
|||On 4 May, 13:47, faux...@.gmail.com wrote:[vbcol=seagreen]
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
>
>
thanks I will try that
|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> If you now the column name you can do a search with object browser in query
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
> "faux...@.gmail.com" wrote:
>
>
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.
|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegr oups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>
|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
------
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
------
|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ------
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ------
thanks I will try that
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
Michael
Use the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
|||If you now the column name you can do a search with object browser in query
analyzer on the database.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"fauxDBA@.gmail.com" wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
>
>
|||On 4 May, 13:47, faux...@.gmail.com wrote:[vbcol=seagreen]
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
>
>
thanks I will try that
|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> If you now the column name you can do a search with object browser in query
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
> "faux...@.gmail.com" wrote:
>
>
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.
|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegr oups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>
|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
------
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
------
|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ------
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ------
thanks I will try that
finding a string somewhere in the MS SQL user tables
Hi,
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
MichaelUse the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael|||If you now the column name you can do a search with object browser in query
analyzer on the database.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL :)
"fauxDBA@.gmail.com" wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> > Hi,
> >
> > I have an application which shows a certain piece of data on the
> > screen, but I do not know which table it comes from (281 user tables).
> >
> > How can I easily find which table has this data ?
> > (without opening each table and scanning through 1000's of rows
> > manually)
> >
> > TIA
> > Michael
>
>|||On 4 May, 13:47, faux...@.gmail.com wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
> > Hi,
> > I have an application which shows a certain piece of data on the
> > screen, but I do not know which table it comes from (281 user tables).
> > How can I easily find which table has this data ?
> > (without opening each table and scanning through 1000's of rows
> > manually)
> > TIA
> > Michael
thanks I will try that|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:
> If you now the column name you can do a search with object browser in query
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL :)
> "faux...@.gmail.com" wrote:
> > Use the profiler ... do the activity again to have a look at that
> > data ... In the backend it would definately show you the table or the
> > SP the application is calling ... Dig in further.
> > Regards
> > Bharat Butani.
> > On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
> > > Hi,
> > > I have an application which shows a certain piece of data on the
> > > screen, but I do not know which table it comes from (281 user tables).
> > > How can I easily find which table has this data ?
> > > (without opening each table and scanning through 1000's of rows
> > > manually)
> > > TIA
> > > Michael
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegroups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
------
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
------|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ------
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ------
thanks I will try that
I have an application which shows a certain piece of data on the
screen, but I do not know which table it comes from (281 user tables).
How can I easily find which table has this data ?
(without opening each table and scanning through 1000's of rows
manually)
TIA
MichaelUse the profiler ... do the activity again to have a look at that
data ... In the backend it would definately show you the table or the
SP the application is calling ... Dig in further.
Regards
Bharat Butani.
On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael|||If you now the column name you can do a search with object browser in query
analyzer on the database.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL :)
"fauxDBA@.gmail.com" wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
>
> Regards
> Bharat Butani.
>
> On May 4, 3:39 pm, Chiken Koma <michaelnewp...@.yahoo.com> wrote:
> > Hi,
> >
> > I have an application which shows a certain piece of data on the
> > screen, but I do not know which table it comes from (281 user tables).
> >
> > How can I easily find which table has this data ?
> > (without opening each table and scanning through 1000's of rows
> > manually)
> >
> > TIA
> > Michael
>
>|||On 4 May, 13:47, faux...@.gmail.com wrote:
> Use the profiler ... do the activity again to have a look at that
> data ... In the backend it would definately show you the table or the
> SP the application is calling ... Dig in further.
> Regards
> Bharat Butani.
> On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
> > Hi,
> > I have an application which shows a certain piece of data on the
> > screen, but I do not know which table it comes from (281 user tables).
> > How can I easily find which table has this data ?
> > (without opening each table and scanning through 1000's of rows
> > manually)
> > TIA
> > Michael
thanks I will try that|||On 4 May, 13:57, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:
> If you now the column name you can do a search with object browser in query
> analyzer on the database.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL :)
> "faux...@.gmail.com" wrote:
> > Use the profiler ... do the activity again to have a look at that
> > data ... In the backend it would definately show you the table or the
> > SP the application is calling ... Dig in further.
> > Regards
> > Bharat Butani.
> > On May 4, 3:39 pm,ChikenKoma<michaelnewp...@.yahoo.com> wrote:
> > > Hi,
> > > I have an application which shows a certain piece of data on the
> > > screen, but I do not know which table it comes from (281 user tables).
> > > How can I easily find which table has this data ?
> > > (without opening each table and scanning through 1000's of rows
> > > manually)
> > > TIA
> > > Michael
thats the problem we dont know the table, in Ingres I used to dump the
whole database to ascii files and do a string search, this would
highlight a file which corresponded to a table, then I could search
probable columns in that table.|||The simplest way is the probably just to query the cataloguef or the tables,
processing the results to create select statement for each of the tables,
then run them until you find the column.
"Chiken Koma" <michaelnewport@.yahoo.com> wrote in message
news:1178275148.581808.186800@.l77g2000hsb.googlegroups.com...
> Hi,
> I have an application which shows a certain piece of data on the
> screen, but I do not know which table it comes from (281 user tables).
> How can I easily find which table has this data ?
> (without opening each table and scanning through 1000's of rows
> manually)
> TIA
> Michael
>|||If it just is the one time thing to search for a string, below is a SP
which I had written a long time back for fun (this is exactly similar
to what Mark had suggested). Use it rarely, and also not at the peak
time of the day.
------
DROP PROCEDURE FIND_IN_DB
GO
CREATE PROCEDURE FIND_IN_DB
@.SEARCHSTR VARCHAR(100),
@.EXACT_MATCH VARCHAR(1) = 'F',
@.MATCH_FULL_WORD VARCHAR(1) = 'F'
AS
BEGIN
DECLARE @.FROM INT
DECLARE @.TO INT
DECLARE @.TABLE_ID INT
DECLARE @.TABLE_NAME SYSNAME
DECLARE @.COLUMN_NAME SYSNAME
DECLARE @.OPERATOR SYSNAME
CREATE TABLE #TEMP_TABLE (
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TABLE_ID INT,
TABLE_NAME SYSNAME)
CREATE TABLE ##RESULT(
SLNO INT IDENTITY(1, 1) PRIMARY KEY,
TYPE VARCHAR(15),
TABLE_NAME VARCHAR(100),
COLUMN_NAME VARCHAR(300),
DATA_TEXT VARCHAR(7000) )
INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
BY NAME
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME)
SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
WHERE TABLE_NAME = @.SEARCHSTR
SET @.FROM = 1
SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
IF @.EXACT_MATCH = 'F'
BEGIN
IF @.MATCH_FULL_WORD = 'T'
SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
ELSE
SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
SELECT @.OPERATOR = ' LIKE '
END
ELSE
SELECT @.OPERATOR = ' = '
WHILE @.FROM <= @.TO
BEGIN
SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
#TEMP_TABLE WHERE SLNO = @.FROM
SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
IF @.EXACT_MATCH <> 'T'
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME LIKE @.SEARCHSTR
ELSE
INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
WHERE COLUMN_NAME = @.SEARCHSTR
WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
BY COLUMN_NAME)
BEGIN
SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
#TEMP_COLUMNS ORDER BY COLUMN_NAME)
EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
DATA_TEXT)
SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
+ ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
+ @.OPERATOR + '''' + @.SEARCHSTR + '''')
DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
END
DROP TABLE #TEMP_COLUMNS
SET @.FROM = @.FROM + 1
END
SELECT * FROM ##RESULT
DROP TABLE #TEMP_TABLE
DROP TABLE ##RESULT
END
GO
-- EXEC FIND_IN_DB 'test%', 'F', 'T'
------|||On 6 May, 12:57, nime...@.gmail.com wrote:
> If it just is the one time thing to search for a string, below is a SP
> which I had written a long time back for fun (this is exactly similar
> to what Mark had suggested). Use it rarely, and also not at the peak
> time of the day.
> ------
> DROP PROCEDURE FIND_IN_DB
> GO
> CREATE PROCEDURE FIND_IN_DB
> @.SEARCHSTR VARCHAR(100),
> @.EXACT_MATCH VARCHAR(1) = 'F',
> @.MATCH_FULL_WORD VARCHAR(1) = 'F'
> AS
> BEGIN
> DECLARE @.FROM INT
> DECLARE @.TO INT
> DECLARE @.TABLE_ID INT
> DECLARE @.TABLE_NAME SYSNAME
> DECLARE @.COLUMN_NAME SYSNAME
> DECLARE @.OPERATOR SYSNAME
> CREATE TABLE #TEMP_TABLE (
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TABLE_ID INT,
> TABLE_NAME SYSNAME)
> CREATE TABLE ##RESULT(
> SLNO INT IDENTITY(1, 1) PRIMARY KEY,
> TYPE VARCHAR(15),
> TABLE_NAME VARCHAR(100),
> COLUMN_NAME VARCHAR(300),
> DATA_TEXT VARCHAR(7000) )
> INSERT #TEMP_TABLE (TABLE_NAME, TABLE_ID)
> SELECT NAME, ID FROM SYSOBJECTS WHERE XTYPE in ('U', 'S') ORDER
> BY NAME
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME)
> SELECT 'TABLE' AS TYPE, TABLE_NAME FROM #TEMP_TABLE
> WHERE TABLE_NAME = @.SEARCHSTR
> SET @.FROM = 1
> SET @.TO = (SELECT MAX(SLNO) FROM #TEMP_TABLE)
> IF @.EXACT_MATCH = 'F'
> BEGIN
> IF @.MATCH_FULL_WORD = 'T'
> SET @.SEARCHSTR = '%[^a-z,^0-9,_]' +
> RTRIM(LTRIM(@.SEARCHSTR)) + '[^a-z,^0-9,_]%'
> ELSE
> SET @.SEARCHSTR = '%' + @.SEARCHSTR + '%'
> SELECT @.OPERATOR = ' LIKE '
> END
> ELSE
> SELECT @.OPERATOR = ' = '
> WHILE @.FROM <= @.TO
> BEGIN
> SELECT @.TABLE_NAME = TABLE_NAME , @.TABLE_ID = TABLE_ID FROM
> #TEMP_TABLE WHERE SLNO = @.FROM
> SELECT NAME AS COLUMN_NAME INTO #TEMP_COLUMNS FROM SYSCOLUMNS
> WHERE ID = @.TABLE_ID AND XTYPE = 167 ORDER BY XTYPE DESC
> IF @.EXACT_MATCH <> 'T'
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME LIKE @.SEARCHSTR
> ELSE
> INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME, DATA_TEXT)
> SELECT 'COLUMN' AS TYPE, @.TABLE_NAME AS TABLE_NAME,
> COLUMN_NAME, COLUMN_NAME FROM #TEMP_COLUMNS
> WHERE COLUMN_NAME = @.SEARCHSTR
> WHILE EXISTS(SELECT TOP 1 COLUMN_NAME FROM #TEMP_COLUMNS ORDER
> BY COLUMN_NAME)
> BEGIN
> SET @.COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM
> #TEMP_COLUMNS ORDER BY COLUMN_NAME)
> EXECUTE('INSERT ##RESULT (TYPE, TABLE_NAME, COLUMN_NAME,
> DATA_TEXT)
> SELECT ''DATA'' AS TYPE, ''' + @.TABLE_NAME + ''' AS
> TABLE_NAME, ''' + @.COLUMN_NAME + ''', '+ @.COLUMN_NAME
> + ' FROM ' + @.TABLE_NAME + ' WHERE ' + @.COLUMN_NAME + ' '
> + @.OPERATOR + '''' + @.SEARCHSTR + '''')
> DELETE #TEMP_COLUMNS WHERE COLUMN_NAME = @.COLUMN_NAME
> END
> DROP TABLE #TEMP_COLUMNS
> SET @.FROM = @.FROM + 1
> END
> SELECT * FROM ##RESULT
> DROP TABLE #TEMP_TABLE
> DROP TABLE ##RESULT
> END
> GO
> -- EXEC FIND_IN_DB 'test%', 'F', 'T'
> ------
thanks I will try that
Wednesday, March 21, 2012
Find user who made table Inserts
I would like to be able to find out what user account made changes to a
few tables on my DB. I am not exactly a SQL DBA but i am sure there are
a few places that i can look. I did not see anything in the SQL Database
logfiles. Anything that i can check."Noah" <noah@.carpathiahost.com> wrote in message
news:%23Y2r7NlEIHA.6120@.TK2MSFTNGP05.phx.gbl...
>I would like to be able to find out what user account made changes to a few
>tables on my DB. I am not exactly a SQL DBA but i am sure there are a few
>places that i can look. I did not see anything in the SQL Database
>logfiles. Anything that i can check.
Unless the designer of your system chose to record that information
elsewhere you would have to use some log mining tool such as:
http://www.lumigent.com/products/le_sql.html
--
David Portas|||Noah,
There is nothing after the fact to tell you who made changes to certain rows
of data. You can set up some methods that would catch future changes, such
as:
1. Use stored procedures for updates to data and have the stored procedure
records the user who made the change.
2. Use triggers on your tables to insert data into a audit table that
records changes,
3. Use SQL Trace and SQL Profiler to track all commands against the
database.
And so on. All of these have some overhead involved and have different
rights issues and limitations. I have listed them in what I believe to be
the lowest to highest server impact.
In SQL Server 2008 there will be a new feature to do this for you in
Enterprise Edition, according to:
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/09/21/elaborations-on-my-sql-server-2008-slides.aspx
http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
RLF
"Noah" <noah@.carpathiahost.com> wrote in message
news:%23Y2r7NlEIHA.6120@.TK2MSFTNGP05.phx.gbl...
>I would like to be able to find out what user account made changes to a few
>tables on my DB. I am not exactly a SQL DBA but i am sure there are a few
>places that i can look. I did not see anything in the SQL Database
>logfiles. Anything that i can check.|||Thanks for the advice.
On 10/19/07 9:49 AM, in article #$hWWblEIHA.6120@.TK2MSFTNGP05.phx.gbl,
"Russell Fields" <russellfields@.nomail.com> wrote:
> Noah,
> There is nothing after the fact to tell you who made changes to certain rows
> of data. You can set up some methods that would catch future changes, such
> as:
> 1. Use stored procedures for updates to data and have the stored procedure
> records the user who made the change.
> 2. Use triggers on your tables to insert data into a audit table that
> records changes,
> 3. Use SQL Trace and SQL Profiler to track all commands against the
> database.
> And so on. All of these have some overhead involved and have different
> rights issues and limitations. I have listed them in what I believe to be
> the lowest to highest server impact.
> In SQL Server 2008 there will be a new feature to do this for you in
> Enterprise Edition, according to:
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/09/21/elaborations-on-my-
> sql-server-2008-slides.aspx
> http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
>
> RLF
> "Noah" <noah@.carpathiahost.com> wrote in message
> news:%23Y2r7NlEIHA.6120@.TK2MSFTNGP05.phx.gbl...
>> I would like to be able to find out what user account made changes to a few
>> tables on my DB. I am not exactly a SQL DBA but i am sure there are a few
>> places that i can look. I did not see anything in the SQL Database
>> logfiles. Anything that i can check.
>
few tables on my DB. I am not exactly a SQL DBA but i am sure there are
a few places that i can look. I did not see anything in the SQL Database
logfiles. Anything that i can check."Noah" <noah@.carpathiahost.com> wrote in message
news:%23Y2r7NlEIHA.6120@.TK2MSFTNGP05.phx.gbl...
>I would like to be able to find out what user account made changes to a few
>tables on my DB. I am not exactly a SQL DBA but i am sure there are a few
>places that i can look. I did not see anything in the SQL Database
>logfiles. Anything that i can check.
Unless the designer of your system chose to record that information
elsewhere you would have to use some log mining tool such as:
http://www.lumigent.com/products/le_sql.html
--
David Portas|||Noah,
There is nothing after the fact to tell you who made changes to certain rows
of data. You can set up some methods that would catch future changes, such
as:
1. Use stored procedures for updates to data and have the stored procedure
records the user who made the change.
2. Use triggers on your tables to insert data into a audit table that
records changes,
3. Use SQL Trace and SQL Profiler to track all commands against the
database.
And so on. All of these have some overhead involved and have different
rights issues and limitations. I have listed them in what I believe to be
the lowest to highest server impact.
In SQL Server 2008 there will be a new feature to do this for you in
Enterprise Edition, according to:
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/09/21/elaborations-on-my-sql-server-2008-slides.aspx
http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
RLF
"Noah" <noah@.carpathiahost.com> wrote in message
news:%23Y2r7NlEIHA.6120@.TK2MSFTNGP05.phx.gbl...
>I would like to be able to find out what user account made changes to a few
>tables on my DB. I am not exactly a SQL DBA but i am sure there are a few
>places that i can look. I did not see anything in the SQL Database
>logfiles. Anything that i can check.|||Thanks for the advice.
On 10/19/07 9:49 AM, in article #$hWWblEIHA.6120@.TK2MSFTNGP05.phx.gbl,
"Russell Fields" <russellfields@.nomail.com> wrote:
> Noah,
> There is nothing after the fact to tell you who made changes to certain rows
> of data. You can set up some methods that would catch future changes, such
> as:
> 1. Use stored procedures for updates to data and have the stored procedure
> records the user who made the change.
> 2. Use triggers on your tables to insert data into a audit table that
> records changes,
> 3. Use SQL Trace and SQL Profiler to track all commands against the
> database.
> And so on. All of these have some overhead involved and have different
> rights issues and limitations. I have listed them in what I believe to be
> the lowest to highest server impact.
> In SQL Server 2008 there will be a new feature to do this for you in
> Enterprise Edition, according to:
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/09/21/elaborations-on-my-
> sql-server-2008-slides.aspx
> http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
>
> RLF
> "Noah" <noah@.carpathiahost.com> wrote in message
> news:%23Y2r7NlEIHA.6120@.TK2MSFTNGP05.phx.gbl...
>> I would like to be able to find out what user account made changes to a few
>> tables on my DB. I am not exactly a SQL DBA but i am sure there are a few
>> places that i can look. I did not see anything in the SQL Database
>> logfiles. Anything that i can check.
>
Monday, March 19, 2012
Find tables w/o primary keys but w/ unique clustered indexes
Hi
I run SQL Server 2000 SP3 on Windows 2000 SP4.
I want to find out, which "user tables" in my DB don't have
a primary key but do have at least one unique index. I
couldn't find any documented way to do this straight away.
So I came up with the following query, which seems to work:
SELECT OBJECT_NAME(id)
FROM sysindexes
WHERE indid > 0 and indid < 255 and (status & 2048) = 0
and (status & 2) <> 0
Does this query seem to be correct and is there any better
(documented) way of doing what I'm trying to do?
--
Thanks,
OskHow about:
SELECT DISTINCT
table_name,
constraint_name
FROM information_schema.table_constraints c
WHERE constraint_type = 'UNIQUE'
AND NOT EXISTS
(SELECT *
FROM information_schema.table_constraints c1
WHERE C1.table_name = c.table_name
and c1.constraint_type = 'PRIMARY KEY')
AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
'IsClustered') = 1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Try,
select distinct
object_name(a.[id])
from
sysindexes as a
left join
(
select
table_name
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
constraint_type = 'PRIMARY KEY'
) as b
on object_name(a.[id]) = b.table_name
where
b.table_name is null
and indexproperty(a.[id], [name], 'IsUnique') = 1
and objectproperty(a.[id], 'IsMSShipped') = 0
go
AMB
"Osk" wrote:
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Thanks!
Shame on me - IDEXPROPERTY was the way to go.
>--Original Message--
>Try,
>select distinct
> object_name(a.[id])
>from
> sysindexes as a
> left join
> (
> select
> table_name
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> constraint_type = 'PRIMARY KEY'
> ) as b
> on object_name(a.[id]) = b.table_name
>where
> b.table_name is null
> and indexproperty(a.[id], [name], 'IsUnique') = 1
> and objectproperty(a.[id], 'IsMSShipped') = 0
>go
>
>AMB
>"Osk" wrote:
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>>
>.
>|||Thanks, Adam.
Still, I wasn't exactly looking for tables with UNIQUE
constraints but rather - with UNIQUE indexes.
>--Original Message--
>How about:
>SELECT DISTINCT
> table_name,
> constraint_name
>FROM information_schema.table_constraints c
>WHERE constraint_type = 'UNIQUE'
> AND NOT EXISTS
> (SELECT *
> FROM information_schema.table_constraints c1
> WHERE C1.table_name = c.table_name
> and c1.constraint_type = 'PRIMARY KEY')
> AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
>'IsClustered') = 1
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.datamanipulation.net
>--
>
>"Osk" <anonymous@.discussions.microsoft.com> wrote in message
>news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>
>.
>|||Lookup the meta data function OBJECTPROPERTY and its various possible values
for the second argument in SQL Server Books Online. You can get the list
using:
SELECT name
FROM sysobjects
WHERE s1.xtype = 'U'
AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
Alternatively, you can also use INFORMATION_SCHEMA views instead of
accessing the system table directly.
--
Anith|||Yes, Anith, thanks!
Though initially I wasn't really interested in finding
tables with UNIQUE constraints but rather with UNIQUE
indexes, which is not the same, is it?
>--Original Message--
>Lookup the meta data function OBJECTPROPERTY and its
various possible values
>for the second argument in SQL Server Books Online. You
can get the list
>using:
>SELECT name
> FROM sysobjects
> WHERE s1.xtype = 'U'
> AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
> AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
>Alternatively, you can also use INFORMATION_SCHEMA views
instead of
>accessing the system table directly.
>--
>Anith
>
>.
>
I run SQL Server 2000 SP3 on Windows 2000 SP4.
I want to find out, which "user tables" in my DB don't have
a primary key but do have at least one unique index. I
couldn't find any documented way to do this straight away.
So I came up with the following query, which seems to work:
SELECT OBJECT_NAME(id)
FROM sysindexes
WHERE indid > 0 and indid < 255 and (status & 2048) = 0
and (status & 2) <> 0
Does this query seem to be correct and is there any better
(documented) way of doing what I'm trying to do?
--
Thanks,
OskHow about:
SELECT DISTINCT
table_name,
constraint_name
FROM information_schema.table_constraints c
WHERE constraint_type = 'UNIQUE'
AND NOT EXISTS
(SELECT *
FROM information_schema.table_constraints c1
WHERE C1.table_name = c.table_name
and c1.constraint_type = 'PRIMARY KEY')
AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
'IsClustered') = 1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Try,
select distinct
object_name(a.[id])
from
sysindexes as a
left join
(
select
table_name
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
constraint_type = 'PRIMARY KEY'
) as b
on object_name(a.[id]) = b.table_name
where
b.table_name is null
and indexproperty(a.[id], [name], 'IsUnique') = 1
and objectproperty(a.[id], 'IsMSShipped') = 0
go
AMB
"Osk" wrote:
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Thanks!
Shame on me - IDEXPROPERTY was the way to go.
>--Original Message--
>Try,
>select distinct
> object_name(a.[id])
>from
> sysindexes as a
> left join
> (
> select
> table_name
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> constraint_type = 'PRIMARY KEY'
> ) as b
> on object_name(a.[id]) = b.table_name
>where
> b.table_name is null
> and indexproperty(a.[id], [name], 'IsUnique') = 1
> and objectproperty(a.[id], 'IsMSShipped') = 0
>go
>
>AMB
>"Osk" wrote:
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>>
>.
>|||Thanks, Adam.
Still, I wasn't exactly looking for tables with UNIQUE
constraints but rather - with UNIQUE indexes.
>--Original Message--
>How about:
>SELECT DISTINCT
> table_name,
> constraint_name
>FROM information_schema.table_constraints c
>WHERE constraint_type = 'UNIQUE'
> AND NOT EXISTS
> (SELECT *
> FROM information_schema.table_constraints c1
> WHERE C1.table_name = c.table_name
> and c1.constraint_type = 'PRIMARY KEY')
> AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
>'IsClustered') = 1
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.datamanipulation.net
>--
>
>"Osk" <anonymous@.discussions.microsoft.com> wrote in message
>news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>
>.
>|||Lookup the meta data function OBJECTPROPERTY and its various possible values
for the second argument in SQL Server Books Online. You can get the list
using:
SELECT name
FROM sysobjects
WHERE s1.xtype = 'U'
AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
Alternatively, you can also use INFORMATION_SCHEMA views instead of
accessing the system table directly.
--
Anith|||Yes, Anith, thanks!
Though initially I wasn't really interested in finding
tables with UNIQUE constraints but rather with UNIQUE
indexes, which is not the same, is it?
>--Original Message--
>Lookup the meta data function OBJECTPROPERTY and its
various possible values
>for the second argument in SQL Server Books Online. You
can get the list
>using:
>SELECT name
> FROM sysobjects
> WHERE s1.xtype = 'U'
> AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
> AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
>Alternatively, you can also use INFORMATION_SCHEMA views
instead of
>accessing the system table directly.
>--
>Anith
>
>.
>
find tables
hi, how to find the user table begin with "abc"(abcTB1, abcTB2...) use
query?Hi,
Use the below command:-
sp_tables 'abc%'
Thanks
Hari
SQL Server MVP
"js" <js@.someone.com> wrote in message
news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
> hi, how to find the user table begin with "abc"(abcTB1, abcTB2...) use
> query?
>|||This would help as well:
select * from sysobjects where name like 'abc%'
Thanks
ZS
"Hari Pra
" wrote:
> Hi,
> Use the below command:-
> sp_tables 'abc%'
> Thanks
> Hari
> SQL Server MVP
> "js" <js@.someone.com> wrote in message
> news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
>
>|||another is:
select table_name
from information_schema.tables
where table_name like 'abc%'
-oj
"js" <js@.someone.com> wrote in message
news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
> hi, how to find the user table begin with "abc"(abcTB1, abcTB2...) use
> query?
>|||Thanks all,
How to put into a record and loop through them?
"oj" <nospam_ojngo@.home.com> wrote in message
news:ejRZl6LQFHA.3496@.TK2MSFTNGP12.phx.gbl...
> another is:
> select table_name
> from information_schema.tables
> where table_name like 'abc%'
> --
> -oj
>
> "js" <js@.someone.com> wrote in message
> news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
>|||You would use a cursor... Cursor examples are documented in Books on line...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"js" <js@.someone.com> wrote in message
news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
> hi, how to find the user table begin with "abc"(abcTB1, abcTB2...) use
> query?
>
query?Hi,
Use the below command:-
sp_tables 'abc%'
Thanks
Hari
SQL Server MVP
"js" <js@.someone.com> wrote in message
news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
> hi, how to find the user table begin with "abc"(abcTB1, abcTB2...) use
> query?
>|||This would help as well:
select * from sysobjects where name like 'abc%'
Thanks
ZS
"Hari Pra
" wrote:> Hi,
> Use the below command:-
> sp_tables 'abc%'
> Thanks
> Hari
> SQL Server MVP
> "js" <js@.someone.com> wrote in message
> news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
>
>|||another is:
select table_name
from information_schema.tables
where table_name like 'abc%'
-oj
"js" <js@.someone.com> wrote in message
news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
> hi, how to find the user table begin with "abc"(abcTB1, abcTB2...) use
> query?
>|||Thanks all,
How to put into a record and loop through them?
"oj" <nospam_ojngo@.home.com> wrote in message
news:ejRZl6LQFHA.3496@.TK2MSFTNGP12.phx.gbl...
> another is:
> select table_name
> from information_schema.tables
> where table_name like 'abc%'
> --
> -oj
>
> "js" <js@.someone.com> wrote in message
> news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
>|||You would use a cursor... Cursor examples are documented in Books on line...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"js" <js@.someone.com> wrote in message
news:%234Sq5zJQFHA.3496@.TK2MSFTNGP12.phx.gbl...
> hi, how to find the user table begin with "abc"(abcTB1, abcTB2...) use
> query?
>
Monday, March 12, 2012
Find records for X previous days
On a webform, I have three button ... [7 days] [15 days] [30 days]
When the user clicks one of the buttons, I want to return their orders for the past X days. The WHERE clause would include something like this (for 7 days):
WHERE (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 7, 102) AND CONVERT(DATETIME, GETDATE(), 102))
How do I parameterize the number of days?
Thanks,
TimI dont think you can, so you will need to do:
WHERE (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 7, 102) AND CONVERT(DATETIME, GETDATE(), 102) AND @.days = 7) OR (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 15, 102) AND CONVERT(DATETIME, GETDATE(), 102) AND @.days = 15) OR (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 30, 102) AND CONVERT(DATETIME, GETDATE(), 102) AND @.days = 30)
Nick|||
When the user clicks one of the buttons, I want to return their orders for the past X days. The WHERE clause would include something like this (for 7 days):
WHERE (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 7, 102) AND CONVERT(DATETIME, GETDATE(), 102))
How do I parameterize the number of days?
Thanks,
TimI dont think you can, so you will need to do:
WHERE (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 7, 102) AND CONVERT(DATETIME, GETDATE(), 102) AND @.days = 7) OR (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 15, 102) AND CONVERT(DATETIME, GETDATE(), 102) AND @.days = 15) OR (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 30, 102) AND CONVERT(DATETIME, GETDATE(), 102) AND @.days = 30)
Nick|||
Try these links for CASE statement and SQL Server DATEDIFF function. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm
http://www.stanford.edu/~bsuter/sql-datecomputations.html
find phonecall records based on user at the time of call
i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show:
PhoneUsers table
PhoneUserID Name StartDate PhoneID
1 john 1/08/2006 1
2 bob 20/08/2006 1
3 fred 2/08/2006 2
etc
Phones table:
PhoneID
1
2
etc
PhoneCalls table:
PhoneCallID PhoneID CallDate Duration
1 1 10/08/2006 25
2 1 23/082006 20
3 2 23/08/2006 20
i want the following result set:
user calldate duration
john 10/08/2006 25
bob 23/08/2006 20
fred 23/08/2006 20
essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data:
the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date
thanks
This should be close:select
U1.Name as [user],
C.CallDate as calldate,
C.Duration as duration
from PhoneCalls as C
left outer join
PhoneUsers as U1
on U1.PhoneID = C.PhoneID
and U1.StartDate <= C.CallDate
and not exists (
select * from PhoneUsers as U2
where U2.PhoneID = U1.PhoneID
and U2.StartDate > U1.StartDate
and U2.StartDate <= C.CallDate
)
Steve Kass
Drew University
http://www.stevekass.com
milo68@.discussions.microsoft.com wrote:
> i have a table of phonecall records detailing amongst other things the
> time and duration of the call. there is a relationship linking this
> table to a table of handsets and from there to a table of users. this
> users table lists the start date the user had the phone. as ever sample
> data might make this easier to show:
>
> PhoneUsers table
>
> PhoneUserID Name StartDate PhoneID
>
> 1 john 1/08/2006 1
>
> 2 bob 20/08/2006 1
>
> 3 fred 2/08/2006 2
>
> etc
>
> Phones table:
>
> PhoneID
>
> 1
>
> 2
>
> etc
>
> PhoneCalls table:
>
> PhoneCallID PhoneID CallDate Duration
>
> 1 1 10/08/2006 25
>
> 2 1 23/082006 20
>
> 3 2 23/08/2006 20
>
> i want the following result set:
>
> user calldate duration
>
> john 10/08/2006 25
>
> bob 23/08/2006 20
>
> fred 23/08/2006 20
>
> essentially i am trying to link the phonecalls to the user of the phone
> at the time of the call. i make the following assumptions regarding the
> data:
>
> the user is responsible for all calls for a given handset from the
> 'startdate' until the next 'startdate' for a different user, if such a
> record exists, if not then for all calls after that date
>
>
>
> thanks
>
>
>
>
>
>
Find out if current user is member of a role
I need a stored procedure to find out if the current user is a member of a certain role.
I want to pass the role name and return a bit to tell whether he is a member or not.
I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.
DECLARE @.usr varchar(32)
SET @.usr = USER
EXEC sp_helpuser @.usr
But if the current user is a member of more than one role it returns multiple rows. So if I could do something like:
DECLARE @.grpName varchar(32)
SELECT * FROM (EXEC sp_helpuser @.usr) WHERE GroupName=@.grpName
IF rowcount > 0 THEN
RETURN 1
ELSE
RETURN 0
END IF
I know that doesn't work so how can I do this?I'm sure that someone out there can do better than this, but you might try:
ALTER PROC spCheckGroup
@.UserName varchar(255), @.GroupName varchar(255)
AS
DECLARE @.Count int
SELECT @.Count = Count(*)
FROM (
select
s1.name as username,
s2.name as groupname
from
dbo.sysusers s1 left join dbo.sysmembers sm on
s1.uid = sm.memberuid
left outer join dbo.sysusers s2 on
sm.groupuid = s2.uid
where
s1.uid < 16383
) t1
WHERE
t1.userName = @.UserName and
t1.GroupName = @.GroupName
If @.Count > 0
Return 1
ELSE
Return 0
Test it with this code:
[/code]
DECLARE @.return_status int
EXEC @.return_status = spCheckGroup 'OAJO-SQLAdmin', 'db_owner'
SELECT 'Return Status' = @.return_status
[/code]|||check: BOL
IS_MEMBER ( { 'group' | 'role' } )|||I'll just crawl back under the rock where I came from...|||LOL.
Thanks for trying.|||I need a stored procedure to work with .NET so here's what I have:
CREATE PROCEDURE IsGroupMember
(
@.groupName nvarchar(32),
@.retVal bit OUTPUT
)
AS
SET @.retVal = IS_MEMBER(@.groupName)
GO
and in Query Analyzer I run this:
DECLARE @.bt bit
EXEC IsGroupMember 'db_owner', @.bt
IF @.bt = 1 print 'member'
ELSE IF @.bt = 0 print 'non-member'
ELSE print 'undefined'
but I keep getting undefined. What's wrong?|||EXEC IsGroupMember 'db_owner', @.bt output
I want to pass the role name and return a bit to tell whether he is a member or not.
I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.
DECLARE @.usr varchar(32)
SET @.usr = USER
EXEC sp_helpuser @.usr
But if the current user is a member of more than one role it returns multiple rows. So if I could do something like:
DECLARE @.grpName varchar(32)
SELECT * FROM (EXEC sp_helpuser @.usr) WHERE GroupName=@.grpName
IF rowcount > 0 THEN
RETURN 1
ELSE
RETURN 0
END IF
I know that doesn't work so how can I do this?I'm sure that someone out there can do better than this, but you might try:
ALTER PROC spCheckGroup
@.UserName varchar(255), @.GroupName varchar(255)
AS
DECLARE @.Count int
SELECT @.Count = Count(*)
FROM (
select
s1.name as username,
s2.name as groupname
from
dbo.sysusers s1 left join dbo.sysmembers sm on
s1.uid = sm.memberuid
left outer join dbo.sysusers s2 on
sm.groupuid = s2.uid
where
s1.uid < 16383
) t1
WHERE
t1.userName = @.UserName and
t1.GroupName = @.GroupName
If @.Count > 0
Return 1
ELSE
Return 0
Test it with this code:
[/code]
DECLARE @.return_status int
EXEC @.return_status = spCheckGroup 'OAJO-SQLAdmin', 'db_owner'
SELECT 'Return Status' = @.return_status
[/code]|||check: BOL
IS_MEMBER ( { 'group' | 'role' } )|||I'll just crawl back under the rock where I came from...|||LOL.
Thanks for trying.|||I need a stored procedure to work with .NET so here's what I have:
CREATE PROCEDURE IsGroupMember
(
@.groupName nvarchar(32),
@.retVal bit OUTPUT
)
AS
SET @.retVal = IS_MEMBER(@.groupName)
GO
and in Query Analyzer I run this:
DECLARE @.bt bit
EXEC IsGroupMember 'db_owner', @.bt
IF @.bt = 1 print 'member'
ELSE IF @.bt = 0 print 'non-member'
ELSE print 'undefined'
but I keep getting undefined. What's wrong?|||EXEC IsGroupMember 'db_owner', @.bt output
Friday, March 9, 2012
find out a user/account permission...
is there an easy to find out an user/account's permsions within a database?
thank you
a list of objects and rights.
such as
select delete insert exec
table1 x x
table2 x
stored proc xSteve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > is there an easy to find out an user/account's permsions within a
> database?
> > thank you
> > a list of objects and rights.
> > such as
> >
> > select delete insert exec
> > table1 x x
> > table2 x
> > stored proc x
> >
> >
>
>|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>
thank you
a list of objects and rights.
such as
select delete insert exec
table1 x x
table2 x
stored proc xSteve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > is there an easy to find out an user/account's permsions within a
> database?
> > thank you
> > a list of objects and rights.
> > such as
> >
> > select delete insert exec
> > table1 x x
> > table2 x
> > stored proc x
> >
> >
>
>|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>
find out a user/account permission...
is there an easy to find out an user/account's permsions within a database?
thank you
a list of objects and rights.
such as
select delete insert exec
table1 x x
table2 x
stored proc xSteve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> database?
>
>|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee
.com
URL http://www.metromilwaukee.com/clintongallagher/
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>
thank you
a list of objects and rights.
such as
select delete insert exec
table1 x x
table2 x
stored proc xSteve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> database?
>
>|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee
.com
URL http://www.metromilwaukee.com/clintongallagher/
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>
find out a user/account permission...
Steve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>
The attached query should work for you, it is based around sysprotects.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>
begin 666 permissions.sql
M<V5L96-T#0H)<75O=&5N86UE*&\N;F%M92D@.05,@.3V)J96-T3F%M90T*+ EC
M87-E(&\N='EP92 -"@.D)=VAE;B G<"<@.=&AE;B G4')O8V5D=7)E)PT*"0EW
M:&5N("=U)R!T:&5N("=486)L92<-"@.D)=VAE;B G='(G('1H96X@.)U1R:6=G
M97(G#0H)"7=H96X@.)V,G('1H96X@.)T-O;G-T<F%I;G0G#0H)"7=H96X@.)W1F
M)R!T:&5N("=&=6YC=&EO;B<-"@.D)=VAE;B G=B<@.=&AE;B G5FEE=R<-"@.EE
M;'-E("=5;FMN;W=N)R!E;F0@.87,@.3V)J96-T5'EP90T*+ EQ=6]T96YA;64H
M=2YN86UE*2!!4R!$0E5S97).86UE#0HL"7-U;2AC87-E('=H96X@.86-T:6]N
M(#T@.,3DU(&%N9"!P<F]T96-T='EP92!I;B H,C T+#(P-2D@.=&AE;B Q("TM
M($=204Y4140-"@.D)=VAE;B!A8W1I;VX@./2 Q.34@.86YD('!R;W1E8W1T>7!E
M(#T@.,C V('1H96X@.+3$@.(" @.(" @.("TM(%)%5D]+140-"@.D)96QS92 P(&5N
M9"D@.87,@.)TE.4T525"<@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M("TM(&YE:71H97(-"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 Q.3,@.86YD
M('!R;W1E8W1T>7!E(&EN("@.R,#0L,C U*2!T:&5N(#$@.( T*"0EW:&5N(&%C
M=&EO;B ](#$Y,R!A;F0@.<')O=&5C='1Y<&4@./2 R,#8@.=&AE;B M,2 @.(" @.
M(" @.( T*"0EE;'-E(# @.96YD*2!A<R G4T5,14-4)R @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.( T*+ ES=6TH8V%S92!W:&5N(&%C=&EO;B ]
M(#$Y-R!A;F0@.<')O=&5C='1Y<&4@.:6X@.*#(P-"PR,#4I('1H96X@.,2 -"@.D)
M=VAE;B!A8W1I;VX@./2 Q.3<@.86YD('!R;W1E8W1T>7!E(#T@.,C V('1H96X@.
M+3$@.#0H)"65L<V4@.,"!E;F0I(&%S("=54$1!5$4G#0HL"7-U;2AC87-E('=H
M96X@.86-T:6]N(#T@.,3DV(&%N9"!P<F]T96-T='EP92!I;B H,C T+#(P-2D@.
M=&AE;B Q( T*"0EW:&5N(&%C=&EO;B ](#$Y-B!A;F0@.<')O=&5C='1Y<&4@.
M/2 R,#8@.=&AE;B M,2 -"@.D)96QS92 P(&5N9"D@.87,@.)T1%3$5412<-"BP)
M<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,C0@.86YD('!R;W1E8W1T>7!E(&EN
M("@.R,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.,C(T(&%N9"!P
M<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.96YD*2!A<R G
M15A%0U5412<-"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R-B!A;F0@.<')O
M=&5C='1Y<&4@.:6X@.*#(P-"PR,#4I('1H96X@.,2 -"@.D)=VAE;B!A8W1I;VX@.
M/2 R-B!A;F0@.<')O=&5C='1Y<&4@./2 R,#8@.=&AE;B M,2 -"@.D)96QS92 P
M(&5N9"D@.87,@.)U)%1D5214Y#15,G#0HL"7-U;2AC87-E('=H96X@.86-T:6]N
M(#T@.,3DX(&%N9"!P<F]T96-T='EP92!I;B H,C T+#(P-2D@.=&AE;B Q( T*
M"0EW:&5N(&%C=&EO;B ](#$Y."!A;F0@.<')O=&5C='1Y<&4@./2 R,#8@.=&AE
M;B M,2 -"@.D)96QS92 P(&5N9"D@.87,@.)T-214%412!404),12<-"BP)<W5M
M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,#,@.86YD('!R;W1E8W1T>7!E(&EN("@.R
M,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.,C S(&%N9"!P<F]T
M96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.96YD*2!A<R G0U)%
M051%($1!5$%"05-%)PT*+ ES=6TH8V%S92!W:&5N(&%C=&EO;B ](#(P-R!A
M;F0@.<')O=&5C='1Y<&4@.:6X@.*#(P-"PR,#4I('1H96X@.,2 -"@.D)=VAE;B!A
M8W1I;VX@./2 R,#<@.86YD('!R;W1E8W1T>7!E(#T@.,C V('1H96X@.+3$@.#0H)
M"65L<V4@.,"!E;F0I(&%S("=#4D5!5$4@.5DE%5R<-"BP)<W5M*&-A<V4@.=VAE
M;B!A8W1I;VX@./2 R,C(@.86YD('!R;W1E8W1T>7!E(&EN("@.R,#0L,C U*2!T
M:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.,C(R(&%N9"!P<F]T96-T='EP92 ]
M(#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.96YD*2!A<R G0U)%051%(%!23T-%
M1%5212<-"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,C@.@.86YD('!R;W1E
M8W1T>7!E(&EN("@.R,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.
M,C(X(&%N9"!P<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.
M96YD*2!A<R G0D%#2U50($1!5$%"05-%)PT*+ ES=6TH8V%S92!W:&5N(&%C
M=&EO;B ](#(S,R!A;F0@.<')O=&5C='1Y<&4@.:6X@.*#(P-"PR,#4I('1H96X@.
M,2 -"@.D)=VAE;B!A8W1I;VX@./2 R,S,@.86YD('!R;W1E8W1T>7!E(#T@.,C V
M('1H96X@.+3$@.#0H)"65L<V4@.,"!E;F0I(&%S("=#4D5!5$4@.1 $5&055,5"<-
M"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,S4@.86YD('!R;W1E8W1T>7!E
M(&EN("@.R,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.,C,U(&%N
M9"!P<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.96YD*2!A
M<R G0D%#2U50($Q/1R<-"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,S8@.
M86YD('!R;W1E8W1T>7!E(&EN("@.R,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.
M86-T:6]N(#T@.,C,V(&%N9"!P<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*
M"0EE;'-E(# @.96YD*2!A<R G0U)%051%(%)53$4G#0IF<F]M#0IS>7-P<F]T
M96-T<R!P#0II;FYE<B!J;VEN('-Y<V]B:F5C=',@.;R!O;B!P+FED(#T@.;RYI
M9 T*:6YN97(@.:F]I;B!S>7-U<V5R<R!U(&]N(' N=6ED(#T@.=2YU:60-"G=H
M97)E(&]B:F5C='!R;W!E<G1Y("AO+FED+" G27--<U-H:7!P960G*2 ](# -
M"F=R;W5P(&)Y(&\N;F%M92P@.;RYT>7!E+"!U+FYA;64-"F]R9&5R(&)Y(&\N
6='EP92P@.;RYN86UE+"!U+FYA;64-"@.``
`
end
|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> database?
>
>
|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>
|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>
The attached query should work for you, it is based around sysprotects.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>
begin 666 permissions.sql
M<V5L96-T#0H)<75O=&5N86UE*&\N;F%M92D@.05,@.3V)J96-T3F%M90T*+ EC
M87-E(&\N='EP92 -"@.D)=VAE;B G<"<@.=&AE;B G4')O8V5D=7)E)PT*"0EW
M:&5N("=U)R!T:&5N("=486)L92<-"@.D)=VAE;B G='(G('1H96X@.)U1R:6=G
M97(G#0H)"7=H96X@.)V,G('1H96X@.)T-O;G-T<F%I;G0G#0H)"7=H96X@.)W1F
M)R!T:&5N("=&=6YC=&EO;B<-"@.D)=VAE;B G=B<@.=&AE;B G5FEE=R<-"@.EE
M;'-E("=5;FMN;W=N)R!E;F0@.87,@.3V)J96-T5'EP90T*+ EQ=6]T96YA;64H
M=2YN86UE*2!!4R!$0E5S97).86UE#0HL"7-U;2AC87-E('=H96X@.86-T:6]N
M(#T@.,3DU(&%N9"!P<F]T96-T='EP92!I;B H,C T+#(P-2D@.=&AE;B Q("TM
M($=204Y4140-"@.D)=VAE;B!A8W1I;VX@./2 Q.34@.86YD('!R;W1E8W1T>7!E
M(#T@.,C V('1H96X@.+3$@.(" @.(" @.("TM(%)%5D]+140-"@.D)96QS92 P(&5N
M9"D@.87,@.)TE.4T525"<@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M("TM(&YE:71H97(-"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 Q.3,@.86YD
M('!R;W1E8W1T>7!E(&EN("@.R,#0L,C U*2!T:&5N(#$@.( T*"0EW:&5N(&%C
M=&EO;B ](#$Y,R!A;F0@.<')O=&5C='1Y<&4@./2 R,#8@.=&AE;B M,2 @.(" @.
M(" @.( T*"0EE;'-E(# @.96YD*2!A<R G4T5,14-4)R @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.( T*+ ES=6TH8V%S92!W:&5N(&%C=&EO;B ]
M(#$Y-R!A;F0@.<')O=&5C='1Y<&4@.:6X@.*#(P-"PR,#4I('1H96X@.,2 -"@.D)
M=VAE;B!A8W1I;VX@./2 Q.3<@.86YD('!R;W1E8W1T>7!E(#T@.,C V('1H96X@.
M+3$@.#0H)"65L<V4@.,"!E;F0I(&%S("=54$1!5$4G#0HL"7-U;2AC87-E('=H
M96X@.86-T:6]N(#T@.,3DV(&%N9"!P<F]T96-T='EP92!I;B H,C T+#(P-2D@.
M=&AE;B Q( T*"0EW:&5N(&%C=&EO;B ](#$Y-B!A;F0@.<')O=&5C='1Y<&4@.
M/2 R,#8@.=&AE;B M,2 -"@.D)96QS92 P(&5N9"D@.87,@.)T1%3$5412<-"BP)
M<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,C0@.86YD('!R;W1E8W1T>7!E(&EN
M("@.R,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.,C(T(&%N9"!P
M<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.96YD*2!A<R G
M15A%0U5412<-"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R-B!A;F0@.<')O
M=&5C='1Y<&4@.:6X@.*#(P-"PR,#4I('1H96X@.,2 -"@.D)=VAE;B!A8W1I;VX@.
M/2 R-B!A;F0@.<')O=&5C='1Y<&4@./2 R,#8@.=&AE;B M,2 -"@.D)96QS92 P
M(&5N9"D@.87,@.)U)%1D5214Y#15,G#0HL"7-U;2AC87-E('=H96X@.86-T:6]N
M(#T@.,3DX(&%N9"!P<F]T96-T='EP92!I;B H,C T+#(P-2D@.=&AE;B Q( T*
M"0EW:&5N(&%C=&EO;B ](#$Y."!A;F0@.<')O=&5C='1Y<&4@./2 R,#8@.=&AE
M;B M,2 -"@.D)96QS92 P(&5N9"D@.87,@.)T-214%412!404),12<-"BP)<W5M
M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,#,@.86YD('!R;W1E8W1T>7!E(&EN("@.R
M,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.,C S(&%N9"!P<F]T
M96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.96YD*2!A<R G0U)%
M051%($1!5$%"05-%)PT*+ ES=6TH8V%S92!W:&5N(&%C=&EO;B ](#(P-R!A
M;F0@.<')O=&5C='1Y<&4@.:6X@.*#(P-"PR,#4I('1H96X@.,2 -"@.D)=VAE;B!A
M8W1I;VX@./2 R,#<@.86YD('!R;W1E8W1T>7!E(#T@.,C V('1H96X@.+3$@.#0H)
M"65L<V4@.,"!E;F0I(&%S("=#4D5!5$4@.5DE%5R<-"BP)<W5M*&-A<V4@.=VAE
M;B!A8W1I;VX@./2 R,C(@.86YD('!R;W1E8W1T>7!E(&EN("@.R,#0L,C U*2!T
M:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.,C(R(&%N9"!P<F]T96-T='EP92 ]
M(#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.96YD*2!A<R G0U)%051%(%!23T-%
M1%5212<-"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,C@.@.86YD('!R;W1E
M8W1T>7!E(&EN("@.R,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.
M,C(X(&%N9"!P<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.
M96YD*2!A<R G0D%#2U50($1!5$%"05-%)PT*+ ES=6TH8V%S92!W:&5N(&%C
M=&EO;B ](#(S,R!A;F0@.<')O=&5C='1Y<&4@.:6X@.*#(P-"PR,#4I('1H96X@.
M,2 -"@.D)=VAE;B!A8W1I;VX@./2 R,S,@.86YD('!R;W1E8W1T>7!E(#T@.,C V
M('1H96X@.+3$@.#0H)"65L<V4@.,"!E;F0I(&%S("=#4D5!5$4@.1 $5&055,5"<-
M"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,S4@.86YD('!R;W1E8W1T>7!E
M(&EN("@.R,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.86-T:6]N(#T@.,C,U(&%N
M9"!P<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*"0EE;'-E(# @.96YD*2!A
M<R G0D%#2U50($Q/1R<-"BP)<W5M*&-A<V4@.=VAE;B!A8W1I;VX@./2 R,S8@.
M86YD('!R;W1E8W1T>7!E(&EN("@.R,#0L,C U*2!T:&5N(#$@.#0H)"7=H96X@.
M86-T:6]N(#T@.,C,V(&%N9"!P<F]T96-T='EP92 ](#(P-B!T:&5N("TQ( T*
M"0EE;'-E(# @.96YD*2!A<R G0U)%051%(%)53$4G#0IF<F]M#0IS>7-P<F]T
M96-T<R!P#0II;FYE<B!J;VEN('-Y<V]B:F5C=',@.;R!O;B!P+FED(#T@.;RYI
M9 T*:6YN97(@.:F]I;B!S>7-U<V5R<R!U(&]N(' N=6ED(#T@.=2YU:60-"G=H
M97)E(&]B:F5C='!R;W!E<G1Y("AO+FED+" G27--<U-H:7!P960G*2 ](# -
M"F=R;W5P(&)Y(&\N;F%M92P@.;RYT>7!E+"!U+FYA;64-"F]R9&5R(&)Y(&\N
6='EP92P@.;RYN86UE+"!U+FYA;64-"@.``
`
end
|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> database?
>
>
|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>
|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>
Subscribe to:
Posts (Atom)