Re: how to list all tables used in a database?

#1

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 (AT) iscar (DOT) co.il> wrote

Quote:

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_ (AT) hotmail_A_ (DOT) com> wrote in message
news:OreCV6qSFHA.1152 (AT) tk2msftngp13 (DOT) 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.

#2

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_ (AT) hotmail_A_ (DOT) com> wrote

Quote:

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 (AT) iscar (DOT) co.il> wrote in message
news:exlUagvSFHA.3636 (AT) TK2MSFTNGP14 (DOT) 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_ (AT) hotmail_A_ (DOT) com> wrote in message
news:OreCV6qSFHA.1152 (AT) tk2msftngp13 (DOT) 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.

#3

yes, I know...

but what filters, events and columns I have to select to accomplish this?

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote

Quote:

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_ (AT) hotmail_A_ (DOT) com> wrote in message
news:er2yWhxSFHA.1896 (AT) TK2MSFTNGP14 (DOT) 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 (AT) iscar (DOT) co.il> wrote in message
news:exlUagvSFHA.3636 (AT) TK2MSFTNGP14 (DOT) 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_ (AT) hotmail_A_ (DOT) com> wrote in message
news:OreCV6qSFHA.1152 (AT) tk2msftngp13 (DOT) 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: how to list all tables used in a database?

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: how to list all tables used in a database? - 04-27-2005 , 06:01 AM

Re: how to list all tables used in a database? - 04-27-2005 , 06:06 AM

Re: how to list all tables used in a database? - 04-27-2005 , 12:00 PM