How To Find Inactive Objects..

Jun 5, 2007

I am trying to find any stored procedures or tables that have not been used in the last 2 months or so. Can anyone recommend me a good way to do this?



SQL 2005 Enterprise Edition SP 2



Thanks in advance.



Kay

View 1 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Find All The Inactive Windows Logins?

Jan 30, 2014

How to query in SQL to retrieve all the disabled Windows Users which are available as records(only) in the SQL Server ?

View 9 Replies View Related

How To Find Out Changes Happened For All Objects

Jul 30, 2013

How i can find out the changes happened in database like modifying functions,table indexes,procedures and adding or removing columns..here in this query

select * from sys.objects
where type IS NOT NULL
and modify_date between '2013-07-21' and '2013-07-29'

but here i am getting created objects list and modifying list.but if i deleted any object it is not showing anything..how can i get the all the changes happened in database between specific dates.

View 3 Replies View Related

How To Find Invalid Objects --After DDL Changes

Sep 28, 2006

Hi

I have a SQL Server 2005 database running. When I run some ddl changes, I want to find all the procs/objects that get invalidated because of object not found error.....

Is there any way that I can look up in sysdepends or other tables to find information about this.

Regards

Imtiaz

View 1 Replies View Related

How To Find Invalid Objects In Database

Apr 24, 2014

I need to find all the invalid objects means which will throw error on execution in a particular DB.

View 1 Replies View Related

Find Database Objects That Contain A String

May 5, 2006

-- This stored procedure will let you search through your database
-- to find various objects that contain a particular string.
-- For example, you may want to see all tables and views that contain
-- a particular column.


use master
IF (object_id('sp_FindReferences') IS NOT NULL)
BEGIN
PRINT 'Dropping: sp_FindReferences'
DROP procedure sp_FindReferences
END
PRINT 'Creating: sp_FindReferences'
GO
CREATE PROCEDURE sp_FindReferences
(
@string varchar(1000) = '',
@ShowReferences char(1) = 'N'
)
AS
/****************************************************************************/
/* */
/* TITLE: sp_FindReferences */
/* */
/* DATE: 18 February, 2004 */
/* */
/* AUTHOR: WILLIAM MCEVOY */
/* */
/****************************************************************************/
/* */
/* DESCRIPTION: SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */
/* */
/****************************************************************************/
set nocount on

declare @errnum int ,
@errors char(1) ,
@rowcnt int ,
@output varchar(255)

select @errnum = 0 ,
@errors = 'N' ,
@rowcnt = 0 ,
@output = ''

/****************************************************************************/
/* INPUT DATA VALIDATION */
/****************************************************************************/


/****************************************************************************/
/* M A I N P R O C E S S I N G */
/****************************************************************************/

-- Create temp table to hold results
create table #Results
(
Name varchar(55),
Type varchar(12),
DateCreated datetime,
ProcLine varchar(4000)
)


IF (@ShowReferences = 'N')
BEGIN
insert into #Results
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END
ELSE
BEGIN
insert into #Results
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = text
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END


IF (@ShowReferences = 'N')
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated
from #Results
order by 2,1
END
ELSE
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated,
ProcLine
from #Results
order by 2,1
END

drop table #Results

GO
IF (object_id('sp_FindReferences') IS NOT NULL)
PRINT 'Procedure created.'
ELSE
PRINT 'Procedure NOT created.'
GO

View 4 Replies View Related

Where Can I Find These Objects Associated With These Permissions In Master?

Nov 8, 2007

I'm trying to identify the objects in master that the role public has select permissions on, but when I run this query, I get 4 results where the default schema is null and the major_id column does not correspond to any records in the sys.all_objects table. Where else can I look to find what objects these are. DBO is listed as the grantor.

I appreciate your help.

SELECT *
FROM SYS.DATABASE_PERMISSIONS P,
SYS.DATABASE_PRINCIPALS R
WHERE P.GRANTEE_PRINCIPAL_ID=R.PRINCIPAL_ID and
permission_name='SELECT' and class_desc='OBJECT_OR_COLUMN' and
r.name='public'
order by r.name desc

View 9 Replies View Related

How To Find Objects That Are Not Following Naming Convention

Apr 1, 2008

Hi All,

I have an assignment to change names of the SPs that are not following Standard naming convention in all databases. For this first I need to find out all the SPs that are not following naming convention. Can anybody help me in finding out the objects that are not following standard naming conventions. It is first step in completing this assignment. If anybody deal with this type of task before please guide me.
Hope any one of you will reply me with more information......

Thanks,
Pradeep

View 9 Replies View Related

How To Find Dependent/Referenced Objects Of A Stored Procedure?

Jan 31, 2007

Hi Frens,

Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?

Thanks a lot for your time.
Regards,
-Ram.

View 11 Replies View Related

SQL Server 2012 :: Altering All Objects To Find Syntax Errors

Jul 25, 2014

How to alter all objects in database i want to find if can any syntax errors in my database after restoring from sql 2008 to 2012. I Can create as test and drop them but trying to find a way to alter proc , views and functions..

View 4 Replies View Related

SQL 2012 :: Find Buffer Cache Usage By DB Objects Within Particular Database

Jun 22, 2015

I am using SQL 2012 and I am trying to find buffer cache usage by DB objects within a particular DB.

I am running the following query

select
name as DB,
objname as db_object_name,
COUNT(name) as cache_page_count,
COUNT('x')*8.0/1024 as size_mb

[Code] ....

Following are the results:-

DB db_object_name cache_page_countsize_mb
TEST_DBNULL 428 3.34375
TEST_DBsysobjvalues 369 2.882812
TEST_DBsyscolpars 44 0.34375
TEST_DBsysssislog 38 0.296875
....
.....

Question- Why am I getting 428 pages for which there is no corresponding DB object? Why are so many pages present in sys.dm_os_buffer_descriptors but are missing from sys.allocation_units.

View 0 Replies View Related

Putting Names Of Objects To Control-flow Loop Creating Objects

Dec 27, 2006

please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help

View 5 Replies View Related

Inactive Tables

Apr 24, 2008

We have 350 tables in our database, I want to drop/delete those table(s) that are inactive. Is there a way to check which tables are not active, so I could drop/delete that table(s).

View 1 Replies View Related

What Is INACTIVE Delete?

Oct 10, 2005

Here are two tables. How do i create an SP for INACTIVE delete and how do i use the UNIQUE constraint to avoid duplication of the NAME columns(logingroup_name and login_name) in these tables?

--Creating Table ref_LoginGroup:-
create table ref_LoginGroup
(
logingroupid tinyint identity(1,1) constraint pklogingroupid primary key clustered,
logingroup_name varchar(25) constraint chkname check(logingroup_name NOT LIKE '% %'),
changed_login_id int,
is_active bit,
lastupdr smalldatetime,
)
--Creating Unique nonclustered Index for ref_LoginGroup:-
create unique nonclustered index idxlgn on ref_LoginGroup(logingroup_name)


--Creating Table ref_Login:-
create table ref_Login
(
login_id int identity(1,1) constraint pkloginid primary key clustered,
login_name varchar(50) constraint chklogname check(login_name NOT LIKE '% %'),
firstname varchar(30),
lastname varchar(30),
logingroupid tinyint constraint fklogingroupid foreign key references ref_LoginGroup(logingroupid),
changed_login_id int,
is_active bit,
lastupdr smalldatetime
)
--Creating Unique nonclustered Index for ref_Login:-
create unique nonclustered index idxl on ref_Login(login_name)

View 20 Replies View Related

Why I Can't Remove The Inactive Log?

May 16, 2007

My db is working in simple recovery mode, when I run "dbcc sqlperf(logspace)", I find the log file is very large and the log usage percentage is 98%. In most case, when I run "backup log db_name with no_log", the log usage percentage will be reduced. but in fact it is no change. I make sure there are no active logs in it. Who can tell me the reason ? thx.

View 1 Replies View Related

SQL Server 2012 :: Select Data From XML - Objects Within Objects?

Nov 20, 2013

passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.

However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.

Below is a code snippet showing what I have so far.

The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo

DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"

[code]....

View 1 Replies View Related

Replication - Subscription Inactive, Why ?

Jul 23, 2003

I have a snapshot & a transcation publication on Server A, and a pull subscription for each on Server B.

The distribution agent on ServerA has the error:

"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."

Any idea what caused this ..... Everything was fine for a week or so.

Does anyone know of any good books or publications on Replication ? I've got some DBA books that cover the basics, but I'd like something that helps explain the oddball situations.

SQL 2K

View 2 Replies View Related

Lock On Inactive Thread Won't Go Away

Feb 29, 2008

Hello,

I am having a problem with the SQL Server 2005 database of our ERP system. We have a scheduled batch that gets stuck because of a lock on a table. The lock remains overnight and halts all other processes (it seems most of them are waiting for this lock to get solved)
I know which table the lock is on and if I kill the lock manually, the batch continues and finishes without a problem

my question is: can I change this behaviour somewhere?

The piece of code that causes this lock is protected, but in a debug log I was able to see that it happens during following actions:

first a select is performed to fetch a certain record in the file that gets locked
next an update is performed with following syntax:

update ... set.... where current of 'pointername'

this update is waiting for a lock to be released before the batch continues.

My guess is that somehow the pointer that is set with the select statements, locks the row and that's why the update cannot be performed.
The strange part is that this lock does NOT occur everytime that this table needs to be updated. And that's why I am stuck and asking help of the real database people :)

If someone can give me a hint on a setup,property, or just a pointer to where I can find more info on the 'why'-part of the lock, I would be very grateful

database is SQL Server 2005 SP1 on win2k3

View 3 Replies View Related

DTS To SSIS ExecutionStatus Inactive

Sep 5, 2007

Hi ,

I have code in SQL Server 2000 (DTS). what is same code to inactive execution status in SSIS
If returnValue <> "" Then

DTSPackage.Steps("DTSStep_DTSDataPumpTask_2").ExecutionStatus = DTSStepExecStat_Inactive

Else

DTSPackage.Steps("DTSStep_DTSDataPumpTask_3").ExecutionStatus = DTSStepExecStat_Inactive

End If



Thanks,
Raja

View 3 Replies View Related

The Subscription Has Been Marked Inactive

Jul 17, 2006

hi all,

i have a snapshot replication running on multiple publisher singel subscriber running on sql2k. i'm frequently encountering these error.

the subscription has been marked inactive and must be reinitialized.
no synch



i've set the subcription to never expire in the publications property tab.

thanks

View 5 Replies View Related

Check For Inactive Database

Sep 21, 2007

Hi

I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).

What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.

Cheers

View 5 Replies View Related

Tool For Disconnecting Inactive Logins??

Aug 15, 2000

Does anyone know if there is a 3rd party tool available to disconnect user logins after being inactive for a period of time? Is this something that can be custom written?

Thanks for any assistance, Jim

View 3 Replies View Related

Problem On Login: User Is Inactive

Aug 28, 2006

hi,i've just restored my db and when i try to login with a user (even ifthe user is "sa"), an error occours.The error is "User sa is currently inactive"How can i solve this problem?Thanks,Massimo

View 1 Replies View Related

Negate RESULT SET, All Inactive Client

Oct 3, 2007

I' d like to list all inactive clients.Inactive client is a client who hasn't had invoice for 2 months.I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clientsaddresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !=''). I select only invoices with date after the interesting date.So now I can list all active clients but i can't negate this resultset to get all inactive clients:SELECT dok_PlatnikId, adr_Nazwa,adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWystFROM vwDok4FSFZGridINNER JOIN adr__EwidON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiek tuWHEREadr__Ewid.adr_Nazwa !=''AND(dok_DataWyst >= convert(datetime,'10/03/2007'))GROUP BY dok_PlatnikId, adr_Nazwa,adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWystIs there any way to negate this set?1. Sorry for my English2. I would appreciate any help:)

View 1 Replies View Related

SSRS Inactive Menu Issue

Jul 20, 2007

Is anyone else having an issue with SSRS menus not being able to be clicked on?



When I roll the mouse over the menu item, it highlights, but when I click, nothing happens.

It affects the top menu items, and any right click menu.



This is an intermittent problem, but it does happen daily. I have to shut down VS to fix.



BobP

View 5 Replies View Related

Report Builder: How To Access Last Inactive Date

Apr 7, 2008

A Contractor has a list of Contracting Companies that he has worked for in his history.

For each row in the "Contracting Company" table, there is an Active Date and an Inactive Date. Each Contracting Company cannot have overlapping dates.

The last row in the table for that Contractor may have a null Inactive Date. That just means that he doesn't know when his contract will end.

In Report Builder, I can access the "Last Active Date", because there is a pre-built formula for "Last Active Date". However, whenever I access the prebuilt formula "Last Inactive Date" - I get the inactive date for the second-last row, because the last row has a null value for the Inactive Date! I want to get the Inactive Date for the last row - not the second last row!

How to do this easily? Thanks!

View 3 Replies View Related

INSERT, UPDATE, And DELETE Statement Checkbox Inactive

Mar 26, 2008

Hi  AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG   

View 1 Replies View Related

INSERT, UPDATE And DELETE Statements Checkbox Inactive

Mar 26, 2008

I have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance

the screenshot links:
http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG 

View 7 Replies View Related

Report Toolbar Inactive - Suspect RsClientPrint ActiveX Problem

Mar 15, 2007

Hi,

I€™m hoping someone might be able to help me.

I have a Development Machine running Windows XP Professional SP2, with SQL Server 2005 including Reporting Services. I am developing an ASP.NET application using VS 2005, and am including Server Side Reports from SQL Server. Everything is working well, except when viewing the Reports.

The Reports when viewed from Reporting Services€™ Report Manager or from within the ASP.NET application only display the first page of the report, displaying the total number of pages as 0. As well, most of the functions on the report toolbar appear to be disabled. This occurs for any report viewed, even sample reports such as the Adventure Works Samples.

The problem only occurs when viewed from within Internet Explorer on the Development Machine. To overcome the problem, I have upgraded SQLServer to SP2, and removed and reinstalled Internet Explorer, but it made no difference. It appears to be related to the rsClientPrint ActiveX addon which should have been installed when the print icon was first selected. It appears not to have installed properly, but up to that point everything worked OK. Selecting the Print icon (or any other icon) since nothing happens. I€™m guessing here, but I suspect that the rsClientPrint addon needs to be reinstalled, but I don€™t know how to go about it. It is not listed within IE addon (where it is on other machines connecting to the Development Machine), nor is it listed within the €œC:WindowsDownloaded Program Files€?. But it is not being downloaded when the Print Icon is selected. I have checked all the Security settings for the environment to ensure that ActiveX programs are downloaded, and everything appears to be in order.

Would anyone be familiar with this problem or have any suggestions on how to go about resolving it? Any help would be appreciated as it is causing significant problems. Strange though, the reports appear fine when viewed from within the Server Manager.

View 1 Replies View Related

Transact SQL :: Set Child Records To Inactive When Parent Record Deleted From Table

Oct 16, 2015

I need to create a trigger to meet following conditions.

When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.

When parent record is deleted child records needs to be inactivated in table.

View 12 Replies View Related

Transact SQL :: Conversion Failed When Converting Varchar Value (inactive) To Data Type Int

Jul 20, 2015

I am getting this error when I run the case statement below. I think I have to convert at some point, but not sure where or how. 

Case when m.dispo = 2 then 'Inactive' else m.value end 'Score'

View 9 Replies View Related

One Or More Subscriptions Have Been Marked Inactive. Drop And Re-create All Subscriptions For This Node...

May 16, 2007

Greetings,



We periodically get the following message in our three server peer-to-peer transactional replication environment:



"One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."



What causes a subscription to be marked inactive? What properties or settings can be adjusted to minimize or eliminate this occurrence?



Everthing seems to be running OK and then we get the message. It afflicts the higest change volume publication on our slowest overseas server.



Thanks,

BCB

View 9 Replies View Related

Integration Services :: Why Toolbox Is Always Inactive (not SSIS Toolbox)

Dec 3, 2015

why the toolbox is always inactive? I am not talking about the SSIS Toolbox but just the toolbox.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved