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...
>
Showing posts with label packages. Show all posts
Showing posts with label packages. Show all posts
Monday, March 12, 2012
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...
>
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...
>
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.
>> >>
>> >>
>> >
>> >
>>
>
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.
>> >>
>> >>
>> >
>> >
>>
>
Wednesday, March 7, 2012
How to launch them?
Hi everyone,
Primary platform is Framework 2.0
Our vb application throws .dtsx by means of the usual methods.
When I want to run packages allocated in my Windows folder I use LoadPackage; when I want to run packages allocated in my MSDB I use LoadFromSqlServer and at the end of the day when
I want to run packages allocated in my File System SSIS folder I use LoadFromDtsServer.
Drawbacks come here according a "new sort of SSIS packages". I'm talking about those packages which are imported from Sql2k from
Database Engine->Management->Legacy->Data Transformation Services.
How do I launch this kind of SSIS packages?
I'm totally stuck with this. Or maybe the problem is easier: the ones aren't ssis but dts?
Thanks in advance for your input,
If you try to execute DTS 2000 packages then you need to use DTS 2000 object model to load them or to create a SSIS package that contains a Execute DTS 2000 Package task.
Hope this helps,
Ovidiu Burlacu
so you're talking about that they really are dts no ssis. ok
How to launch them?
Hi everyone,
Primary platform is Framework 2.0
Our vb application throws .dtsx by means of the usual methods.
When I want to run packages allocated in my Windows folder I use LoadPackage; when I want to run packages allocated in my MSDB I use LoadFromSqlServer and at the end of the day when
I want to run packages allocated in my File System SSIS folder I use LoadFromDtsServer.
Drawbacks come here according a "new sort of SSIS packages". I'm talking about those packages which are imported from Sql2k from
Database Engine->Management->Legacy->Data Transformation Services.
How do I launch this kind of SSIS packages?
I'm totally stuck with this. Or maybe the problem is easier: the ones aren't ssis but dts?
Thanks in advance for your input,
If you try to execute DTS 2000 packages then you need to use DTS 2000 object model to load them or to create a SSIS package that contains a Execute DTS 2000 Package task.
Hope this helps,
Ovidiu Burlacu
so you're talking about that they really are dts no ssis. ok
Subscribe to:
Posts (Atom)