how to list all tables used in a database?

Hi,
I want to list all the tables used in a particular database during the
execution of a lot of DTS packages.
I think the profiler could help me, but what is the configuration to
accomplish this?
event, columns, filters...
thanks for your guide
Jerome.Hi
The below script find an emtpy tables , modify it for your needs
use pubs
-- create table and do not populate it
create table EmptyTable (c1 int)
go
-- create a cursor to go through each table in a database
-- and print the name of any empty tables
DECLARE @.SQLString nvarchar (255),
@.ParmDefinition nvarchar (255)
DECLARE @.tablename sysname, @.Empty char (1)
DECLARE FindEmptyTables CURSOR READ_ONLY
FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN FindEmptyTables
FETCH NEXT FROM FindEmptyTables INTO @.tablename
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
@.Empty = ''N'' ELSE set @.Empty = ''Y'''
SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
EXECUTE sp_executesql
@.SQLString,
@.ParmDefinition,
@.tablename = @.tablename,
@.Empty = @.Empty OUTPUT
IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
FETCH NEXT FROM FindEmptyTables INTO @.tablename
END
CLOSE FindEmptyTables
DEALLOCATE FindEmptyTables
GO
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
> Hi,
> I want to list all the tables used in a particular database during the
> execution of a lot of DTS packages.
> I think the profiler could help me, but what is the configuration to
> accomplish this?
> event, columns, filters...
> thanks for your guide
> Jerome.
>|||I don't want to list tables availables, I want to list table REALLY used
during the loading process.
I mean tables which is part of a Select statement.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
> Hi
> The below script find an emtpy tables , modify it for your needs
> use pubs
> -- create table and do not populate it
> create table EmptyTable (c1 int)
> go
> -- create a cursor to go through each table in a database
> -- and print the name of any empty tables
> DECLARE @.SQLString nvarchar (255),
> @.ParmDefinition nvarchar (255)
> DECLARE @.tablename sysname, @.Empty char (1)
> DECLARE FindEmptyTables CURSOR READ_ONLY
> FOR SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> OPEN FindEmptyTables
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> WHILE (@.@.fetch_status = 0)
> BEGIN
> SET @.SQLString = N'IF EXISTS (SELECT * FROM ' + @.tablename + ') set
> @.Empty = ''N'' ELSE set @.Empty = ''Y'''
> SET @.ParmDefinition = N'@.tablename sysname, @.Empty char(1) OUTPUT'
> EXECUTE sp_executesql
> @.SQLString,
> @.ParmDefinition,
> @.tablename = @.tablename,
> @.Empty = @.Empty OUTPUT
> IF @.Empty = 'Y' PRINT @.tablename + ' is empty'
> FETCH NEXT FROM FindEmptyTables INTO @.tablename
> END
> CLOSE FindEmptyTables
> DEALLOCATE FindEmptyTables
> GO
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:OreCV6qSFHA.1152@.tk2msftngp13.phx.gbl...
>|||Ok, run SQL Server Profiler to identify those tables , save the data into a
table in SQL Server and make a query to get report.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
> I don't want to list tables availables, I want to list table REALLY used
> during the loading process.
> I mean tables which is part of a Select statement.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:exlUagvSFHA.3636@.TK2MSFTNGP14.phx.gbl...
>|||yes, I know...
but what filters, events and columns I have to select to accomplish this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uUm3IlxSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Ok, run SQL Server Profiler to identify those tables , save the data into
> a
> table in SQL Server and make a query to get report.
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:er2yWhxSFHA.1896@.TK2MSFTNGP14.phx.gbl...
>