Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************
************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AN
D
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space u
sed (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************
************************************[vbc
ol=seagreen]
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2
AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space
used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))[/vbcol]
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL

> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************************************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FL
This uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||FLX
1)sp_spaceused in the BOL
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
************************************************** **************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
************************************************** **************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL

> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>

Determine if database objects are being accessed

Is there anyway of determining if a view or function is being accessed?
I've moved a lot of objects between schemas and to prevent any code
breaking used views and functions to 'redirect' to the new schema.
Is there any way to determine if anything is accessing these objects so
I know I'm safe to remove them? The profiler doesn't seem to be
able to do it. I'm using SQL Server 2005.
Hi
AFAIK there is not any very simple method of doing this, that can be 100%
guaranteed.
If you have scripted all your stored procedures etc. then you could use a
textual search to find references to these views. As you are already using
functions then you could add auditing to them.
John
"Mives" wrote:

> Is there anyway of determining if a view or function is being accessed?
> I've moved a lot of objects between schemas and to prevent any code
> breaking used views and functions to 'redirect' to the new schema.
> Is there any way to determine if anything is accessing these objects so
> I know I'm safe to remove them? The profiler doesn't seem to be
> able to do it. I'm using SQL Server 2005.
>
|||I think that those queries might do the job:
--check stored procedures
select specific_name
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%ObjectName%'
--check views
select TABLE_NAME
from INFORMATION_SCHEMA.VIEWS
where VIEW_DEFINITION like '%ObjectName%'
The queries will help only for objects in the database, but if you
have an application that uses direct SQL statements, you'll get runtime
errors. You can try and use the profiler to see if external
applications use the old names in direct statements. You can also try
to create a synonym with the old name that will point to the new name
(didn't try it myself, but I think that this should work)
Adi
|||A clunky, but effective way to track if someone is using the view or
trigger is:
1.) VIEW - add a trigger on the view to write to a table everytime the
view is accessed
2.) TRIGGER - have the trigger write to a table everytime the trigger
fires, as part of the trigger
You can include timestamp and system_user data in your table
Adi wrote:
> I think that those queries might do the job:
> --check stored procedures
> select specific_name
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_DEFINITION like '%ObjectName%'
> --check views
> select TABLE_NAME
> from INFORMATION_SCHEMA.VIEWS
> where VIEW_DEFINITION like '%ObjectName%'
> The queries will help only for objects in the database, but if you
> have an application that uses direct SQL statements, you'll get runtime
> errors. You can try and use the profiler to see if external
> applications use the old names in direct statements. You can also try
> to create a synonym with the old name that will point to the new name
> (didn't try it myself, but I think that this should work)
> Adi
|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:[vbcol=seagreen]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
|||Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@.gmail.com wrote:[vbcol=seagreen]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
|||On 23 Oct 2006 09:40:08 -0700, "Mives" <michaelives@.gmail.com> wrote:

>Is it possible to have a Trigger that is fired when a view is read
>selected from?
No.
|||Hi
If you never update the views change them into functions.
John
"Mives" wrote:

> Is it possible to have a Trigger that is fired when a view is read
> selected from?
> tootsuite@.gmail.com wrote:
>

Determine if database objects are being accessed

Is there anyway of determining if a view or function is being accessed?
I've moved a lot of objects between schemas and to prevent any code
breaking used views and functions to 'redirect' to the new schema.
Is there any way to determine if anything is accessing these objects so
I know I'm safe to remove them? The profiler doesn't seem to be
able to do it. I'm using SQL Server 2005.Hi
AFAIK there is not any very simple method of doing this, that can be 100%
guaranteed.
If you have scripted all your stored procedures etc. then you could use a
textual search to find references to these views. As you are already using
functions then you could add auditing to them.
John
"Mives" wrote:

> Is there anyway of determining if a view or function is being accessed?
> I've moved a lot of objects between schemas and to prevent any code
> breaking used views and functions to 'redirect' to the new schema.
> Is there any way to determine if anything is accessing these objects so
> I know I'm safe to remove them? The profiler doesn't seem to be
> able to do it. I'm using SQL Server 2005.
>|||I think that those queries might do the job:
--check stored procedures
select specific_name
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%ObjectName%'
--check views
select TABLE_NAME
from INFORMATION_SCHEMA.VIEWS
where VIEW_DEFINITION like '%ObjectName%'
The queries will help only for objects in the database, but if you
have an application that uses direct SQL statements, you'll get runtime
errors. You can try and use the profiler to see if external
applications use the old names in direct statements. You can also try
to create a synonym with the old name that will point to the new name
(didn't try it myself, but I think that this should work)
Adi|||A clunky, but effective way to track if someone is using the view or
trigger is:
1.) VIEW - add a trigger on the view to write to a table everytime the
view is accessed
2.) TRIGGER - have the trigger write to a table everytime the trigger
fires, as part of the trigger
You can include timestamp and system_user data in your table
Adi wrote:
> I think that those queries might do the job:
> --check stored procedures
> select specific_name
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_DEFINITION like '%ObjectName%'
> --check views
> select TABLE_NAME
> from INFORMATION_SCHEMA.VIEWS
> where VIEW_DEFINITION like '%ObjectName%'
> The queries will help only for objects in the database, but if you
> have an application that uses direct SQL statements, you'll get runtime
> errors. You can try and use the profiler to see if external
> applications use the old names in direct statements. You can also try
> to create a synonym with the old name that will point to the new name
> (didn't try it myself, but I think that this should work)
> Adi|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:[vbcol=seagreen]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:|||Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@.gmail.com wrote:[vbcol=seagreen]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:|||On 23 Oct 2006 09:40:08 -0700, "Mives" <michaelives@.gmail.com> wrote:

>Is it possible to have a Trigger that is fired when a view is read
>selected from?
No.|||Hi
If you never update the views change them into functions.
John
"Mives" wrote:

> Is it possible to have a Trigger that is fired when a view is read
> selected from?
> tootsuite@.gmail.com wrote:
>sql

Determine if database objects are being accessed

Is there anyway of determining if a view or function is being accessed?
I've moved a lot of objects between schemas and to prevent any code
breaking used views and functions to 'redirect' to the new schema.
Is there any way to determine if anything is accessing these objects so
I know I'm safe to remove them? The profiler doesn't seem to be
able to do it. I'm using SQL Server 2005.Hi
AFAIK there is not any very simple method of doing this, that can be 100%
guaranteed.
If you have scripted all your stored procedures etc. then you could use a
textual search to find references to these views. As you are already using
functions then you could add auditing to them.
John
"Mives" wrote:
> Is there anyway of determining if a view or function is being accessed?
> I've moved a lot of objects between schemas and to prevent any code
> breaking used views and functions to 'redirect' to the new schema.
> Is there any way to determine if anything is accessing these objects so
> I know I'm safe to remove them? The profiler doesn't seem to be
> able to do it. I'm using SQL Server 2005.
>|||I think that those queries might do the job:
--check stored procedures
select specific_name
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%ObjectName%'
--check views
select TABLE_NAME
from INFORMATION_SCHEMA.VIEWS
where VIEW_DEFINITION like '%ObjectName%'
The queries will help only for objects in the database, but if you
have an application that uses direct SQL statements, you'll get runtime
errors. You can try and use the profiler to see if external
applications use the old names in direct statements. You can also try
to create a synonym with the old name that will point to the new name
(didn't try it myself, but I think that this should work)
Adi|||A clunky, but effective way to track if someone is using the view or
trigger is:
1.) VIEW - add a trigger on the view to write to a table everytime the
view is accessed
2.) TRIGGER - have the trigger write to a table everytime the trigger
fires, as part of the trigger
You can include timestamp and system_user data in your table
Adi wrote:
> I think that those queries might do the job:
> --check stored procedures
> select specific_name
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_DEFINITION like '%ObjectName%'
> --check views
> select TABLE_NAME
> from INFORMATION_SCHEMA.VIEWS
> where VIEW_DEFINITION like '%ObjectName%'
> The queries will help only for objects in the database, but if you
> have an application that uses direct SQL statements, you'll get runtime
> errors. You can try and use the profiler to see if external
> applications use the old names in direct statements. You can also try
> to create a synonym with the old name that will point to the new name
> (didn't try it myself, but I think that this should work)
> Adi|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
> > I think that those queries might do the job:
> >
> > --check stored procedures
> > select specific_name
> > from INFORMATION_SCHEMA.ROUTINES
> > where ROUTINE_DEFINITION like '%ObjectName%'
> >
> > --check views
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.VIEWS
> > where VIEW_DEFINITION like '%ObjectName%'
> >
> > The queries will help only for objects in the database, but if you
> > have an application that uses direct SQL statements, you'll get runtime
> > errors. You can try and use the profiler to see if external
> > applications use the old names in direct statements. You can also try
> > to create a synonym with the old name that will point to the new name
> > (didn't try it myself, but I think that this should work)
> >
> > Adi|||Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@.gmail.com wrote:
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
> > I think that those queries might do the job:
> >
> > --check stored procedures
> > select specific_name
> > from INFORMATION_SCHEMA.ROUTINES
> > where ROUTINE_DEFINITION like '%ObjectName%'
> >
> > --check views
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.VIEWS
> > where VIEW_DEFINITION like '%ObjectName%'
> >
> > The queries will help only for objects in the database, but if you
> > have an application that uses direct SQL statements, you'll get runtime
> > errors. You can try and use the profiler to see if external
> > applications use the old names in direct statements. You can also try
> > to create a synonym with the old name that will point to the new name
> > (didn't try it myself, but I think that this should work)
> >
> > Adi|||On 23 Oct 2006 09:40:08 -0700, "Mives" <michaelives@.gmail.com> wrote:
>Is it possible to have a Trigger that is fired when a view is read
>selected from?
No.|||Hi
If you never update the views change them into functions.
John
"Mives" wrote:
> Is it possible to have a Trigger that is fired when a view is read
> selected from?
> tootsuite@.gmail.com wrote:
> > A clunky, but effective way to track if someone is using the view or
> > trigger is:
> >
> > 1.) VIEW - add a trigger on the view to write to a table everytime the
> > view is accessed
> >
> > 2.) TRIGGER - have the trigger write to a table everytime the trigger
> > fires, as part of the trigger
> >
> > You can include timestamp and system_user data in your table
> >
> >
> > Adi wrote:
> > > I think that those queries might do the job:
> > >
> > > --check stored procedures
> > > select specific_name
> > > from INFORMATION_SCHEMA.ROUTINES
> > > where ROUTINE_DEFINITION like '%ObjectName%'
> > >
> > > --check views
> > > select TABLE_NAME
> > > from INFORMATION_SCHEMA.VIEWS
> > > where VIEW_DEFINITION like '%ObjectName%'
> > >
> > > The queries will help only for objects in the database, but if you
> > > have an application that uses direct SQL statements, you'll get runtime
> > > errors. You can try and use the profiler to see if external
> > > applications use the old names in direct statements. You can also try
> > > to create a synonym with the old name that will point to the new name
> > > (didn't try it myself, but I think that this should work)
> > >
> > > Adi
>

Detecting Custom Objects Used by SQL Server 2000 objects

Hello,
I work as a software engineer and we have installed third party and custom
in-house dlls and ocx on our SQL Server 2000 server to support our business
applications. These dlls and ocx are referenced by the database
objects(jobs, storedprocedures). What I need to do is to generate a list of
all the dlls and ocx that our SQL Server reference, so that I can copy these
files out to our new future SQL Server box.
Does anyone here know how to do this? Is there a system table in one of the
system databases that keep track of the information I'm seeking?
Thanks you.
Scott Yu
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> Hello,
> I work as a software engineer and we have installed third party and custom
> in-house dlls and ocx on our SQL Server 2000 server to support our
> business
> applications. These dlls and ocx are referenced by the database
> objects(jobs, storedprocedures). What I need to do is to generate a list
> of
> all the dlls and ocx that our SQL Server reference, so that I can copy
> these
> files out to our new future SQL Server box.
> Does anyone here know how to do this? Is there a system table in one of
> the
> system databases that keep track of the information I'm seeking?
>
Unfortunately, you will have to dig through the text of the stored
procedures for calls to sp_oacreate.
set textsize 64000
exec sp_msforeachdb '
select text from syscomments
where text like ''%sp_oacreate%'''
This will give you the progid's of the com components used by sql server.
They must be copied to the new server and registered there, along with all
of their dependencies.
This is an exceedingly difficult thing for a DBA to get right. You may need
to track down the owners of each component to sucessfully move it to the new
server.
David
|||Hey David,
Thanks for the script. I pretty much did the same thing by scripting out
the storedprocedures and jobs, but that was before I learned of
sp_msforeachdb, thanks to you. The hardest part is trying to locate all the
dependent dlls. Another dll hell.
Thank you David.
Scott Yu
"David Browne" wrote:

> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> Unfortunately, you will have to dig through the text of the stored
> procedures for calls to sp_oacreate.
> set textsize 64000
> exec sp_msforeachdb '
> select text from syscomments
> where text like ''%sp_oacreate%'''
> This will give you the progid's of the com components used by sql server.
> They must be copied to the new server and registered there, along with all
> of their dependencies.
> This is an exceedingly difficult thing for a DBA to get right. You may need
> to track down the owners of each component to sucessfully move it to the new
> server.
> David
>
>
|||One more thing to remember when you are searching using the LIKE operation:
"_" is a wildcard character meaning "any single character." When you want
the literal "_", you'll want to use brackets like so:
SELECT [name]
FROM syscomments
WHERE [text] LIKE '%sp[_]oacreate%"
Sincerely,
Anthony Thomas
"Scott Yu" wrote:
[vbcol=seagreen]
> Hey David,
> Thanks for the script. I pretty much did the same thing by scripting out
> the storedprocedures and jobs, but that was before I learned of
> sp_msforeachdb, thanks to you. The hardest part is trying to locate all the
> dependent dlls. Another dll hell.
> Thank you David.
> Scott Yu
> "David Browne" wrote:
|||Thank you very much Anthony. What is the difference between "%" and "_" in
TL SQL?
Scott
"AnthonyThomas" wrote:
[vbcol=seagreen]
> One more thing to remember when you are searching using the LIKE operation:
> "_" is a wildcard character meaning "any single character." When you want
> the literal "_", you'll want to use brackets like so:
> SELECT [name]
> FROM syscomments
> WHERE [text] LIKE '%sp[_]oacreate%"
> Sincerely,
>
> Anthony Thomas
>
> "Scott Yu" wrote:
|||Never mind. After re-reading your reply, I understoodd the difference
between "%" and "_".
Thank you.
"AnthonyThomas" wrote:
[vbcol=seagreen]
> One more thing to remember when you are searching using the LIKE operation:
> "_" is a wildcard character meaning "any single character." When you want
> the literal "_", you'll want to use brackets like so:
> SELECT [name]
> FROM syscomments
> WHERE [text] LIKE '%sp[_]oacreate%"
> Sincerely,
>
> Anthony Thomas
>
> "Scott Yu" wrote:
sql

Detecting Custom Objects Used by SQL Server 2000 objects

Hello,
I work as a software engineer and we have installed third party and custom
in-house dlls and ocx on our SQL Server 2000 server to support our business
applications. These dlls and ocx are referenced by the database
objects(jobs, storedprocedures). What I need to do is to generate a list of
all the dlls and ocx that our SQL Server reference, so that I can copy these
files out to our new future SQL Server box.
Does anyone here know how to do this? Is there a system table in one of the
system databases that keep track of the information I'm seeking?
Thanks you.
Scott Yu"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> Hello,
> I work as a software engineer and we have installed third party and custom
> in-house dlls and ocx on our SQL Server 2000 server to support our
> business
> applications. These dlls and ocx are referenced by the database
> objects(jobs, storedprocedures). What I need to do is to generate a list
> of
> all the dlls and ocx that our SQL Server reference, so that I can copy
> these
> files out to our new future SQL Server box.
> Does anyone here know how to do this? Is there a system table in one of
> the
> system databases that keep track of the information I'm seeking?
>
Unfortunately, you will have to dig through the text of the stored
procedures for calls to sp_oacreate.
set textsize 64000
exec sp_msforeachdb '
select text from syscomments
where text like ''%sp_oacreate%'''
This will give you the progid's of the com components used by sql server.
They must be copied to the new server and registered there, along with all
of their dependencies.
This is an exceedingly difficult thing for a DBA to get right. You may need
to track down the owners of each component to sucessfully move it to the new
server.
David|||Hey David,
Thanks for the script. I pretty much did the same thing by scripting out
the storedprocedures and jobs, but that was before I learned of
sp_msforeachdb, thanks to you. The hardest part is trying to locate all th
e
dependent dlls. Another dll hell.
Thank you David.
Scott Yu
"David Browne" wrote:

> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> Unfortunately, you will have to dig through the text of the stored
> procedures for calls to sp_oacreate.
> set textsize 64000
> exec sp_msforeachdb '
> select text from syscomments
> where text like ''%sp_oacreate%'''
> This will give you the progid's of the com components used by sql server.
> They must be copied to the new server and registered there, along with all
> of their dependencies.
> This is an exceedingly difficult thing for a DBA to get right. You may ne
ed
> to track down the owners of each component to sucessfully move it to the n
ew
> server.
> David
>
>|||One more thing to remember when you are searching using the LIKE operation:
"_" is a wildcard character meaning "any single character." When you want
the literal "_", you'll want to use brackets like so:
SELECT [name]
FROM syscomments
WHERE [text] LIKE '%sp[_]oacreate%"
Sincerely,
Anthony Thomas
"Scott Yu" wrote:
[vbcol=seagreen]
> Hey David,
> Thanks for the script. I pretty much did the same thing by scripting out
> the storedprocedures and jobs, but that was before I learned of
> sp_msforeachdb, thanks to you. The hardest part is trying to locate all
the
> dependent dlls. Another dll hell.
> Thank you David.
> Scott Yu
> "David Browne" wrote:
>|||Thank you very much Anthony. What is the difference between "%" and "_" in
TL SQL?
Scott
"AnthonyThomas" wrote:
[vbcol=seagreen]
> One more thing to remember when you are searching using the LIKE operation
:
> "_" is a wildcard character meaning "any single character." When you want
> the literal "_", you'll want to use brackets like so:
> SELECT [name]
> FROM syscomments
> WHERE [text] LIKE '%sp[_]oacreate%"
> Sincerely,
>
> Anthony Thomas
>
> "Scott Yu" wrote:
>|||Never mind. After re-reading your reply, I understoodd the difference
between "%" and "_".
Thank you.
"AnthonyThomas" wrote:
[vbcol=seagreen]
> One more thing to remember when you are searching using the LIKE operation
:
> "_" is a wildcard character meaning "any single character." When you want
> the literal "_", you'll want to use brackets like so:
> SELECT [name]
> FROM syscomments
> WHERE [text] LIKE '%sp[_]oacreate%"
> Sincerely,
>
> Anthony Thomas
>
> "Scott Yu" wrote:
>

Detecting Custom Objects Used by SQL Server 2000 objects

Hello,
I work as a software engineer and we have installed third party and custom
in-house dlls and ocx on our SQL Server 2000 server to support our business
applications. These dlls and ocx are referenced by the database
objects(jobs, storedprocedures). What I need to do is to generate a list of
all the dlls and ocx that our SQL Server reference, so that I can copy these
files out to our new future SQL Server box.
Does anyone here know how to do this? Is there a system table in one of the
system databases that keep track of the information I'm seeking?
Thanks you.
Scott Yu"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> Hello,
> I work as a software engineer and we have installed third party and custom
> in-house dlls and ocx on our SQL Server 2000 server to support our
> business
> applications. These dlls and ocx are referenced by the database
> objects(jobs, storedprocedures). What I need to do is to generate a list
> of
> all the dlls and ocx that our SQL Server reference, so that I can copy
> these
> files out to our new future SQL Server box.
> Does anyone here know how to do this? Is there a system table in one of
> the
> system databases that keep track of the information I'm seeking?
>
Unfortunately, you will have to dig through the text of the stored
procedures for calls to sp_oacreate.
set textsize 64000
exec sp_msforeachdb '
select text from syscomments
where text like ''%sp_oacreate%'''
This will give you the progid's of the com components used by sql server.
They must be copied to the new server and registered there, along with all
of their dependencies.
This is an exceedingly difficult thing for a DBA to get right. You may need
to track down the owners of each component to sucessfully move it to the new
server.
David|||Hey David,
Thanks for the script. I pretty much did the same thing by scripting out
the storedprocedures and jobs, but that was before I learned of
sp_msforeachdb, thanks to you. The hardest part is trying to locate all the
dependent dlls. Another dll hell.
Thank you David.
Scott Yu
"David Browne" wrote:
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> > Hello,
> >
> > I work as a software engineer and we have installed third party and custom
> > in-house dlls and ocx on our SQL Server 2000 server to support our
> > business
> > applications. These dlls and ocx are referenced by the database
> > objects(jobs, storedprocedures). What I need to do is to generate a list
> > of
> > all the dlls and ocx that our SQL Server reference, so that I can copy
> > these
> > files out to our new future SQL Server box.
> >
> > Does anyone here know how to do this? Is there a system table in one of
> > the
> > system databases that keep track of the information I'm seeking?
> >
> Unfortunately, you will have to dig through the text of the stored
> procedures for calls to sp_oacreate.
> set textsize 64000
> exec sp_msforeachdb '
> select text from syscomments
> where text like ''%sp_oacreate%'''
> This will give you the progid's of the com components used by sql server.
> They must be copied to the new server and registered there, along with all
> of their dependencies.
> This is an exceedingly difficult thing for a DBA to get right. You may need
> to track down the owners of each component to sucessfully move it to the new
> server.
> David
>
>|||One more thing to remember when you are searching using the LIKE operation:
"_" is a wildcard character meaning "any single character." When you want
the literal "_", you'll want to use brackets like so:
SELECT [name]
FROM syscomments
WHERE [text] LIKE '%sp[_]oacreate%"
Sincerely,
Anthony Thomas
"Scott Yu" wrote:
> Hey David,
> Thanks for the script. I pretty much did the same thing by scripting out
> the storedprocedures and jobs, but that was before I learned of
> sp_msforeachdb, thanks to you. The hardest part is trying to locate all the
> dependent dlls. Another dll hell.
> Thank you David.
> Scott Yu
> "David Browne" wrote:
> >
> > "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> > news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> > > Hello,
> > >
> > > I work as a software engineer and we have installed third party and custom
> > > in-house dlls and ocx on our SQL Server 2000 server to support our
> > > business
> > > applications. These dlls and ocx are referenced by the database
> > > objects(jobs, storedprocedures). What I need to do is to generate a list
> > > of
> > > all the dlls and ocx that our SQL Server reference, so that I can copy
> > > these
> > > files out to our new future SQL Server box.
> > >
> > > Does anyone here know how to do this? Is there a system table in one of
> > > the
> > > system databases that keep track of the information I'm seeking?
> > >
> >
> > Unfortunately, you will have to dig through the text of the stored
> > procedures for calls to sp_oacreate.
> >
> > set textsize 64000
> > exec sp_msforeachdb '
> > select text from syscomments
> > where text like ''%sp_oacreate%'''
> >
> > This will give you the progid's of the com components used by sql server.
> > They must be copied to the new server and registered there, along with all
> > of their dependencies.
> >
> > This is an exceedingly difficult thing for a DBA to get right. You may need
> > to track down the owners of each component to sucessfully move it to the new
> > server.
> >
> > David
> >
> >
> >|||Thank you very much Anthony. What is the difference between "%" and "_" in
TL SQL?
Scott
"AnthonyThomas" wrote:
> One more thing to remember when you are searching using the LIKE operation:
> "_" is a wildcard character meaning "any single character." When you want
> the literal "_", you'll want to use brackets like so:
> SELECT [name]
> FROM syscomments
> WHERE [text] LIKE '%sp[_]oacreate%"
> Sincerely,
>
> Anthony Thomas
>
> "Scott Yu" wrote:
> > Hey David,
> >
> > Thanks for the script. I pretty much did the same thing by scripting out
> > the storedprocedures and jobs, but that was before I learned of
> > sp_msforeachdb, thanks to you. The hardest part is trying to locate all the
> > dependent dlls. Another dll hell.
> >
> > Thank you David.
> >
> > Scott Yu
> >
> > "David Browne" wrote:
> >
> > >
> > > "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> > > news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> > > > Hello,
> > > >
> > > > I work as a software engineer and we have installed third party and custom
> > > > in-house dlls and ocx on our SQL Server 2000 server to support our
> > > > business
> > > > applications. These dlls and ocx are referenced by the database
> > > > objects(jobs, storedprocedures). What I need to do is to generate a list
> > > > of
> > > > all the dlls and ocx that our SQL Server reference, so that I can copy
> > > > these
> > > > files out to our new future SQL Server box.
> > > >
> > > > Does anyone here know how to do this? Is there a system table in one of
> > > > the
> > > > system databases that keep track of the information I'm seeking?
> > > >
> > >
> > > Unfortunately, you will have to dig through the text of the stored
> > > procedures for calls to sp_oacreate.
> > >
> > > set textsize 64000
> > > exec sp_msforeachdb '
> > > select text from syscomments
> > > where text like ''%sp_oacreate%'''
> > >
> > > This will give you the progid's of the com components used by sql server.
> > > They must be copied to the new server and registered there, along with all
> > > of their dependencies.
> > >
> > > This is an exceedingly difficult thing for a DBA to get right. You may need
> > > to track down the owners of each component to sucessfully move it to the new
> > > server.
> > >
> > > David
> > >
> > >
> > >|||Never mind. After re-reading your reply, I understoodd the difference
between "%" and "_".
Thank you.
"AnthonyThomas" wrote:
> One more thing to remember when you are searching using the LIKE operation:
> "_" is a wildcard character meaning "any single character." When you want
> the literal "_", you'll want to use brackets like so:
> SELECT [name]
> FROM syscomments
> WHERE [text] LIKE '%sp[_]oacreate%"
> Sincerely,
>
> Anthony Thomas
>
> "Scott Yu" wrote:
> > Hey David,
> >
> > Thanks for the script. I pretty much did the same thing by scripting out
> > the storedprocedures and jobs, but that was before I learned of
> > sp_msforeachdb, thanks to you. The hardest part is trying to locate all the
> > dependent dlls. Another dll hell.
> >
> > Thank you David.
> >
> > Scott Yu
> >
> > "David Browne" wrote:
> >
> > >
> > > "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> > > news:C95F6558-D80C-44F7-8E2D-65ED12C13721@.microsoft.com...
> > > > Hello,
> > > >
> > > > I work as a software engineer and we have installed third party and custom
> > > > in-house dlls and ocx on our SQL Server 2000 server to support our
> > > > business
> > > > applications. These dlls and ocx are referenced by the database
> > > > objects(jobs, storedprocedures). What I need to do is to generate a list
> > > > of
> > > > all the dlls and ocx that our SQL Server reference, so that I can copy
> > > > these
> > > > files out to our new future SQL Server box.
> > > >
> > > > Does anyone here know how to do this? Is there a system table in one of
> > > > the
> > > > system databases that keep track of the information I'm seeking?
> > > >
> > >
> > > Unfortunately, you will have to dig through the text of the stored
> > > procedures for calls to sp_oacreate.
> > >
> > > set textsize 64000
> > > exec sp_msforeachdb '
> > > select text from syscomments
> > > where text like ''%sp_oacreate%'''
> > >
> > > This will give you the progid's of the com components used by sql server.
> > > They must be copied to the new server and registered there, along with all
> > > of their dependencies.
> > >
> > > This is an exceedingly difficult thing for a DBA to get right. You may need
> > > to track down the owners of each component to sucessfully move it to the new
> > > server.
> > >
> > > David
> > >
> > >
> > >