Ygthkb

Are small insurances worth it

Where is this quote about overcoming the impossible said in "Interstellar"?

Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?

Has Wakanda ever accepted refugees?

Meaning of word ягоза

I encountered my boss during an on-site interview at another company. Should I bring it up when seeing him next time?

What is the meaning of "notice to quit at once" and "Lotty points”

1970s scifi/horror novel where protagonist is used by a crablike creature to feed its larvae, goes mad, and is defeated by retraumatising him

How to fix my table, centering of columns

How to disable or uninstall iTunes under High Sierra without disabling SIP

Are all UTXOs locked by an address spent in a transaction?

PTIJ: Why can't I sing about soda on certain days?

PTIJ: Aharon, King of Egypt

Is there a full canon version of Tyrion's jackass/honeycomb joke?

A bug in Excel? Conditional formatting for marking duplicates also highlights unique value

Specific Chinese carabiner QA?

How to kill a localhost:8080

I can't die. Who am I?

In which way proportional valves are controlled solely by current?

is 'sed' thread safe

Why did the Cray-1 have 8 parity bits per word?

Why is it "take a leak?"

Was it really inappropriate to write a pull request for the company I interviewed with?

Why would the IRS ask for birth certificates or even audit a small tax return?

I need to figure out the last time that a function was used by SQL Server. How can I do this?

How can I tell if a SQL Server database is still being used?Sql Server Resource Governor - track IOSupport both stored procedures and functions with same TSQL code base?Can queries containing deterministic user defined functions be cached in mysql query cache?Script that alters all stored procedure on the databaseHow can I find last access time for users or specific users to all databases in SQL ServerQuery last time a view, funcion or sp was last used/readFind which functions are usedProduction database substantially bigger than DevHow to Develop Union Reports from Single Tenant Databases?

1

I work with databases that contain thousands of stored procedures and hundreds of functions. I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for functions. Any ideas?

sql-server sql-server-2012 functions

share|improve this question

edited Jan 29 at 4:45

Paul White♦

52.9k14281457

asked Jul 21 '15 at 16:48

user71064user71064

61

bumped to the homepage by Community♦ 12 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

1

I work with databases that contain thousands of stored procedures and hundreds of functions. I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for functions. Any ideas?

sql-server sql-server-2012 functions

share|improve this question

edited Jan 29 at 4:45

Paul White♦

52.9k14281457

asked Jul 21 '15 at 16:48

user71064user71064

61

bumped to the homepage by Community♦ 12 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

I work with databases that contain thousands of stored procedures and hundreds of functions. I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for functions. Any ideas?

sql-server sql-server-2012 functions

share|improve this question

edited Jan 29 at 4:45

Paul White♦

52.9k14281457

asked Jul 21 '15 at 16:48

user71064user71064

61

share|improve this question

edited Jan 29 at 4:45

Paul White♦

52.9k14281457

asked Jul 21 '15 at 16:48

user71064user71064

61

share|improve this question

edited Jan 29 at 4:45

Paul White♦

52.9k14281457

asked Jul 21 '15 at 16:48

user71064user71064

61

edited Jan 29 at 4:45

Paul White♦

52.9k14281457

edited Jan 29 at 4:45

Paul White♦

52.9k14281457

asked Jul 21 '15 at 16:48

user71064user71064

61

asked Jul 21 '15 at 16:48

user71064user71064

61

1 Answer
1

active

oldest

votes

0

You can use Change Data Capture or Change tracking to analyse the required changes here

You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions

Also,

You can get a possible idea as mentioned here:using below query

SELECT usecounts

FROM sys.dm_exec_cached_plans 

CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction') 

So basically you have to find a proper way to analyse the usage.

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Jul 21 '15 at 17:16

KASQLDBAKASQLDBA

5,47261944

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
  
  – user71064
  Jul 21 '15 at 19:50
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your usecounts query gets me part of the way there.
  
  – user71064
  Jul 21 '15 at 19:59
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f107650%2fi-need-to-figure-out-the-last-time-that-a-function-was-used-by-sql-server-how-c%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

You can use Change Data Capture or Change tracking to analyse the required changes here

You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions

Also,

You can get a possible idea as mentioned here:using below query

SELECT usecounts

FROM sys.dm_exec_cached_plans 

CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction') 

So basically you have to find a proper way to analyse the usage.

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Jul 21 '15 at 17:16

KASQLDBAKASQLDBA

5,47261944

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
  
  – user71064
  Jul 21 '15 at 19:50
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your usecounts query gets me part of the way there.
  
  – user71064
  Jul 21 '15 at 19:59
  

  
  
  
  

add a comment | 

0

You can use Change Data Capture or Change tracking to analyse the required changes here

You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions

Also,

You can get a possible idea as mentioned here:using below query

SELECT usecounts

FROM sys.dm_exec_cached_plans 

CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction') 

So basically you have to find a proper way to analyse the usage.

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Jul 21 '15 at 17:16

KASQLDBAKASQLDBA

5,47261944

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
  
  – user71064
  Jul 21 '15 at 19:50
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your usecounts query gets me part of the way there.
  
  – user71064
  Jul 21 '15 at 19:59
  

  
  
  
  

add a comment | 

You can use Change Data Capture or Change tracking to analyse the required changes here

You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions

Also,

You can get a possible idea as mentioned here:using below query

SELECT usecounts

FROM sys.dm_exec_cached_plans 

CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction') 

So basically you have to find a proper way to analyse the usage.

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Jul 21 '15 at 17:16

KASQLDBAKASQLDBA

5,47261944

SELECT usecounts

FROM sys.dm_exec_cached_plans 

CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction') 

share|improve this answer

edited May 23 '17 at 12:40

Community♦

1

answered Jul 21 '15 at 17:16

KASQLDBAKASQLDBA

5,47261944

edited May 23 '17 at 12:40

Community♦

1

edited May 23 '17 at 12:40

Community♦

1

answered Jul 21 '15 at 17:16

KASQLDBAKASQLDBA

5,47261944

answered Jul 21 '15 at 17:16

KASQLDBAKASQLDBA

5,47261944