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
"Jéjé" <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
> "Jéjé" <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.
>>
>|||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.
"Jéjé" <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...
> > 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
> >
> > "Jéjé" <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.
> >>
> >>
> >
> >
>|||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.
> "Jéjé" <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...
>> > 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
>> >
>> > "Jéjé" <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.
>> >>
>> >>
>> >
>> >
>>
>