Finding Missing Records

I have 18 tables that are all related by the primary key. When I join all
the fields together for reporting, it only shows the records that have all
the data filled in. How can I find which tables don't have a record?
Thanks,
DrewLEFT JOIN
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>I have 18 tables that are all related by the primary key. When I join all
>the fields together for reporting, it only shows the records that have all
>the data filled in. How can I find which tables don't have a record?
> Thanks,
> Drew
>|||Didn't think it would be that easy! Thought I would have to use an EXIST
query for this, and couldn't figure out how to incorporate all the tables
into 1 query...
Thanks,
Drew
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
> LEFT JOIN
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>|||SELECT *
FROM Table1
LEFT JOIN Table2
WHERE Table2.Field1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||OOPS, left out the ON clause:
SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||>> When I join all the fields together for reporting, it only shows the
Depending on your requirements, you can use OUTER JOINs or correlated
subqueries to retrieve all rows in one table which does not have a matching
value for a joining column in another table.
Anith

finding missing dates in a sequence

I have 200 tables that have data entered into the daily. I need to identify
all dates that are missing from the tables.
for example:
date column1 column2
1/1/2005 5 20
2/1/2005 67 35
4/1/2005 3 17
5/1/2005 9 6
8/1/2005 7 99
I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
can you please assist?
jayi should have also said i do not want to have a lookup table, ie a table tha
t
permenantly stores dates.
cheers jay
jay
"jay" wrote:

> I have 200 tables that have data entered into the daily. I need to identi
fy
> all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
> can you please assist?
> --
> jay|||How about a temporary lookup table?
DROP TABLE #tmp_data
CREATE TABLE #tmp_data ( entry_date DATETIME PRIMARY KEY, col1 INT NOT NULL,
col2 INT NOT NULL )
SET NOCOUNT ON
INSERT INTO #tmp_data VALUES ( '20050101', 5, 20 )
INSERT INTO #tmp_data VALUES ( '20050102', 67, 35 )
INSERT INTO #tmp_data VALUES ( '20050104', 3, 17 )
INSERT INTO #tmp_data VALUES ( '20050105', 9, 6 )
INSERT INTO #tmp_data VALUES ( '20050108', 7, 99 )
DROP TABLE #tmp_lookup
CREATE TABLE #tmp_lookup ( entry_date DATETIME PRIMARY KEY )
DECLARE @.i INT
DECLARE @.stop INT
DECLARE @.min_date DATETIME
DECLARE @.max_date DATETIME
-- Initialise
SET @.i = 0
-- Calculate the range of dates to be added to the temp lookup table
SELECT
@.min_date = MIN( entry_date ),
@.max_date = MAX( entry_date )
FROM #tmp_data
SET @.stop = DATEDIFF( day, @.min_date, @.max_date ) + 1
-- Add the dates to the lookup table
WHILE @.i < @.stop
BEGIN
INSERT INTO #tmp_lookup SELECT DATEADD( day, @.i, @.min_date )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- Show the missing values
SELECT t1.*
FROM #tmp_lookup t1
LEFT JOIN #tmp_data t2 ON t1.entry_date = t2.entry_date
WHERE t2.entry_date IS NULL
This could even be wrapped in a parameterized stored procedure.
Let me know how you get on.
Damien
"jay" wrote:
> i should have also said i do not want to have a lookup table, ie a table t
hat
> permenantly stores dates.
> cheers jay
> --
> jay
>
> "jay" wrote:
>|||jay <jay@.discussions.microsoft.com> wrote:
> I have 200 tables that have data entered into the daily. I need to
> identify all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
> can you please assist?
It's difficult to generate data that isn't there with an SQL query. Your
best bet is to write a stored procedure that does the job.
Another approach is to find wholes in the data sequence. Not exactly what
you want but this might work (untested):
select t1.ts, t2.mts
from tab t1, (
select min(ts) as mts
from tab tx
where tx.ts > t1.ts
) t2
where datediff('dd', t1.ts, t2.mts) > 1
order by t1.ts
Kind regards
robert|||"jay" <jay@.discussions.microsoft.com> wrote in message
news:BE8860E2-EB68-4EA0-A389-B5D53996608E@.microsoft.com...
> "jay" wrote:
>
to identify
> i should have also said i do not want to have a lookup table, ie a
table that
> permenantly stores dates.
> cheers jay
> --
> jay
>
jay,
Why?
Books on SQL and RDBMs recommend it. Many top names on the subjects
recommend it. It's the way to go.
Sincerely,
Chris O.|||>i should have also said i do not want to have a lookup table, ie a table
>that
> permenantly stores dates.
WHY NOT? Do you also tell the doctor, protect me from the flu, but don't
bring any of that flu vaccine near me!
A calendar table seems to be exactly what you need, and is going to be far
more efficient than looping solutions or generating your entire date range
on the fly every time. Please read http://www.aspfaq.com/2519|||because...the calendar table would need to be kept up to date...who would do
that? you do not know the restrictions on the situation so please do not so
easily pass judgement when you do not know all the issues!
I appreciate any help NOT judgements from ill informed people!
cheers jay
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:efmMbhe$FHA.3568@.TK2MSFTNGP09.phx.gbl...
> WHY NOT? Do you also tell the doctor, protect me from the flu, but don't
> bring any of that flu vaccine near me!
> A calendar table seems to be exactly what you need, and is going to be far
> more efficient than looping solutions or generating your entire date range
> on the fly every time. Please read http://www.aspfaq.com/2519
>
>|||"Jay Walker" <jay@.bladecomputing.com.au> wrote in message
news:eCF4kyf$FHA.3064@.TK2MSFTNGP10.phx.gbl...
> because...the calendar table would need to be kept up to
date...who would do
> that? you do not know the restrictions on the situation so please
do not so
> easily pass judgement when you do not know all the issues!
> I appreciate any help NOT judgements from ill informed people!
> cheers jay
>
Jay Walker,
Aaron is among the best informed people around here.
Also, you did not explain the restrictions of your situation. I
asked, earlier, what those restrictions were, and still have no
answer.
You mentioned: "because...the calendar table would need to be kept
up to date...who would do that?"
What do you mean? A calendar table is loaded, and that is that.
There is no "maintenance" (at least not in our lifetimes).
Sincerely,
Chris O.|||Chris2 (rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com) writes:
> What do you mean? A calendar table is loaded, and that is that.
> There is no "maintenance" (at least not in our lifetimes).
Depends on what you fill it with. If you fill it with dates, and only
dates, you can fill it up until 2150 or so. And for Jay's problem this
would do.
But for a more elaborate calendar that keeps track of business days,
there is of course maintenance to do, as holidays are changed. For instance,
my pocket calendar for 2005 printed May 16th as red, and June 6th as
black, when the days come, May 16th was a busiess day and June 6th was not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||jay (jay@.discussions.microsoft.com) writes:
> I have 200 tables that have data entered into the daily. I need to
> identify all dates that are missing from the tables.
> for example:
> date column1 column2
> 1/1/2005 5 20
> 2/1/2005 67 35
> 4/1/2005 3 17
> 5/1/2005 9 6
> 8/1/2005 7 99
>
> I need to find that 3/1/2005, 6/1/2005 and 7/1/2005 is missing
Here is a query that does not use a calendar table. It will not list all
dates though, only the first and last date in an interval. Also, performance
is not likely to be fantastic. To that end a calendar table will be
better. For this reason, I'm including a script that fills up a dates
table with all dates from 1990 to 2149.
Here is the query (runs in Northwind):
SELECT gapstart, MIN(gapend)
FROM (select gapstart = dateadd(DAY, 1, A.OrderDate)
FROM Orders A
WHERE NOT EXISTS
(SELECT *
FROM Orders B
WHERE B.OrderDate = dateadd(DAY, 1, A.OrderDate))) X
JOIN (select gapend = dateadd(DAY, -1, A.OrderDate)
FROM Orders A
WHERE NOT EXISTS
(SELECT *
FROM Orders B
WHERE B.OrderDate = dateadd(DAY, -1, A.OrderDate))) Y
ON gapstart <= gapend
GROUP BY gapstart
ORDER BY gapstart
And here is the script:
CREATE TABLE dates (
thedate aba_date NOT NULL,
CONSTRAINT pk_dates PRIMARY KEY (thedate)
)
-- Make sure it's empty.
TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)
-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'
SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) +
' rows into #numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

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,
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.

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:

Finding Duplicate Foreign Keys

Hi

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

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

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

Thanks in AdvanceIs this helpful?

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

The following SQL will pick out duplicates only

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

Field1
--
3
6

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

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

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

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

with CTEsql

finding columns

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

finding columns

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

Finding affected tables

I am attempting to document a sql server 2000 based accounting system.
Is there any way to see what tables a stored procedure affects
without diving into the code?

Regards,

Ty"Tyler Hudson" <tylerh@.allpax.com> wrote in message
news:804fa945.0403020849.fcf8f0b@.posting.google.co m...
> I am attempting to document a sql server 2000 based accounting system.
> Is there any way to see what tables a stored procedure affects
> without diving into the code?
>
> Regards,
> Ty

You can try sp_depends, although it may not be completely accurate,
depending on the order in which objects were created.

Simon

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

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

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

Finding a column in all tables within the DB

How do I list all tables that contain a particular column? For example, how do I list all tables that contain the column lastname within my database called manager? Thanx.

Quote:

Originally Posted by Rob0000

How do I list all tables that contain a particular column? For example, how do I list all tables that contain the column lastname within my database called manager? Thanx.

i ran into this issue the other day

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%lastname %' )

if your doing this in SQL server just open up a sql command and execute this

Finding a column in a database

Hi all,

How do I find all tables containing a column (say a column including
the string 'value')?
Thanks

BrunoSELECT sysCol.Name, sysType.name
FROM syscolumns sysCol
INNER JOIN sysobjects sysObj ON sysCol.id = sysObj.id
INNER JOIN systypes sysType on sysCol.xtype = sysType.xtype
WHERE sysObj.name ='<TABLE NAME>'

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||If you mean a column with 'value' in the column name (not in the data),
then there are a couple of ways:

select object_name(id), name
from syscolumns
where name like '%value%'

Or if you want a portable solution, you can use the INFORMATION_SCHEMA
views:

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%value%'

Books Online has more information about syscolumns and the views.

Simon

Find value match in SQL table

Find value match in SQL table

Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?

Taod for ms sql server has that feature to compare rows from 2 tables..

I nkow that are theare also others but Toad i did use it..

Sorry for my bad english

|||

There is a tablediff.exe that comes with SQL Server 2005, if you are using 2005.

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.
>

find unique identifier through multiple tables

>In one table there is GUID as a primary key and another GUID as
foreign key. But there is no relation defined to what table this
foreign key refers [I think you mean that you have no REFERENCES
clause?]. I suppose the link [sic: reference] between these two tables
is purely managed programmatically. <<

I worked for a company that wrote crap like that when I first moved to
Austin. They are still in business, but down from ~1200 employees to
~200 and are now outsourcing their Indian operations to China as they
shrink.

This kind of thing falls apart in about a year. You get orphans all
over the schema (we choked a hard disk for a relatively small custom
sales commission package). There is no ways to validate or verify a
GUID; you need a proper key instead of a bad attempt at pointer
chains. The "pseudo Foreign Key" will be referenced by Cabbages and
Kings simply because all GUIDs can be compared (like pointers or other
exposed physical locators).

You really need to throw this thing out. But if you cannot, then
update your resume.--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>In one table there is GUID as a primary key and another GUID as

foreign key. But there is no relation defined to what table this
foreign key refers [I think you mean that you have no REFERENCES
clause?]. I suppose the link [sic: reference] between these two tables
is purely managed programmatically. <<
>
I worked for a company that wrote crap like that when I first moved to
Austin. They are still in business, but down from ~1200 employees to
~200 and are now outsourcing their Indian operations to China as they
shrink.
>
This kind of thing falls apart in about a year. You get orphans all
over the schema (we choked a hard disk for a relatively small custom
sales commission package). There is no ways to validate or verify a
GUID; you need a proper key instead of a bad attempt at pointer
chains. The "pseudo Foreign Key" will be referenced by Cabbages and
Kings simply because all GUIDs can be compared (like pointers or other
exposed physical locators).

He can add REFERENCES clauses (once he figures out which ones should
be added, and cleans up any existing exceptions), surely?|||>He can add REFERENCES clauses (once he figures out which ones should be added, and cleans up any existing exceptions), surely? <<

Then he will have mimicked a 1970's pointer chain DB in SQL instead of
making this a properly designed RDBMS.

For example, if I use an VIN for an automobile, I can verify the VIN
by going to the automobile, the DMV, insurance company, etc. But if I
use a GUID (or any other hardware generated value), I have no trusted
external source for verification.

I do not have a good way to validate it, in fact. The magical
universal GUID might be used for an automobile, a squid or Britney
Spears!

Find top 10 tables which have worst statistics

hi guys,
I use following command to find out statistic of table, but how do I find t
op 10 tables which have worst statistics in a database?
DBCC SHOW_STATISTICS ( table , target )Kevin,
Maybe using SQL-DMO...I think you'd have a little bit more control of the ou
tput there.
However the timing of when the statistics were last updated might provide yo
u with some insight as to which tables may have the worst statistics.
This query might get you started off of the timing of the last stats update
(not fully tested):
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.id = i.id and STATS_DATE(i.id, i.indid) IS NOT NULL and i.name NOT
LIKE '_WA%'
and i.name NOT LIKE '%sys%'
ORDER BY 2
Do you have the automatic update statistics set for the database?
HTH
Jerry
"Kevin" <pearl_77@.hotmail.com> wrote in message news:%235WH%23mGxFHA.916@.TK2
MSFTNGP10.phx.gbl...
hi guys,
I use following command to find out statistic of table, but how do I find t
op 10 tables which have worst statistics in a database?
DBCC SHOW_STATISTICS ( table , target )|||yes, I have automatic update statistics set on databases.
but I dont' think that will guarantee statistics to be perfect.
I just want to find out which tables have worst statistics. I found an arti
cle in following link, it's very helpful.
http://www.sqlservercentral.com/scr...utions/1069.asp
thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message news:ur44dEHxFHA.27
92@.tk2msftngp13.phx.gbl...
Kevin,
Maybe using SQL-DMO...I think you'd have a little bit more control of the ou
tput there.
However the timing of when the statistics were last updated might provide yo
u with some insight as to which tables may have the worst statistics.
This query might get you started off of the timing of the last stats update
(not fully tested):
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.id = i.id and STATS_DATE(i.id, i.indid) IS NOT NULL and i.name NOT
LIKE '_WA%'
and i.name NOT LIKE '%sys%'
ORDER BY 2
Do you have the automatic update statistics set for the database?
HTH
Jerry
"Kevin" <pearl_77@.hotmail.com> wrote in message news:%235WH%23mGxFHA.916@.TK2
MSFTNGP10.phx.gbl...
hi guys,
I use following command to find out statistic of table, but how do I find t
op 10 tables which have worst statistics in a database?
DBCC SHOW_STATISTICS ( table , target )

Find the nearest date of one column of a table from another table

I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
ER1.CurrencyCd
Can anyone help me with this ?
Regards,
Rajeev RajputHi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query :)
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi,
It is working but i have one issue with it.
When Lockdt is NULL i am getting this part as 1 -->>>>>
select top 1 ExchangeRate from ExchangeRates ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc
rather i should get it as NULL
Could you please help ?
Omnibuzz thanks in advance :)
Omnibuzz wrote:

> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query :)
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc),0),0),0))),
4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/

Find the coumns in the reference

Given a table A, I need to find all the tables that are in PK-FK with A and the columns in the reference. I can get the tables that have the FK relation through sysreferences or sysconstraints or sysforeignkeys but I have not been able to find out how to identify the specific column that is in the relation. Any one has any idea?

Sample:

SELECT * FROM sysreferences WHERE fkeyid = Object_Id('TableA')

Thanks.

Have you tried a system procedure sp_helpconstraint? You can get more detailed information about constraints including PK/FK of specified table. For example:

sp_helpconstraint 'TableA'

|||Thanks for the reply Iori_Jay. I managed to get something working.

find text string in database

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

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

blindman|||Ya mean like:

USE Northwind
GO

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

SET NOCOUNT ON

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

SELECT @.Sargable = 'Beer'

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

OPEN insaneCursor

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

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

SELECT * FROM myTable99 WHERE Occurs <> 0

Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor

GO

DROP TABLE myTable99
GO

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

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

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

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

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

Are Margarittas a hobby?