Auditing my all SPs/Views/Functions in Today's Date

• waseem.shahzad 45937

  SSC Eights!

  Points: 853

  More actions

  December 21, 2011 at 6:03 am

  #262142

  Hi

  We need to run Many SPs , Functions and Views But SP are most redundant usually. I want to Ensure how many SPs i have deployed on specific DB Filtered By their Creation Date and Time.

  It is the best way to fetch out the Audit Data at the end of day.

  Thanks.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 21, 2011 at 6:22 am

  #1424176

  sys.objects has got all that info for you;

  something like this is pretty close, i bet:

  SELECT

  schema_name(schema_id) As SchemaName,

  name as ObjectName,

  create_date

  FROM sys.objects

  WHERE type_desc IN(

  'AGGREGATE_FUNCTION',

  'SQL_SCALAR_FUNCTION',

  'SQL_INLINE_TABLE_VALUED_FUNCTION',

  'SQL_TABLE_VALUED_FUNCTION',

  'SQL_STORED_PROCEDURE','VIEW')

  ORDER BY create_date DESC

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• waseem.shahzad 45937

  SSC Eights!

  Points: 853

  More actions

  December 21, 2011 at 6:26 am

  #1424180

  Can you put a Date check on this?

  SELECT

  schema_name(schema_id) As SchemaName,

  name as ObjectName,

  create_date

  FROM sys.objects

  WHERE type_desc IN(

  'AGGREGATE_FUNCTION',

  'SQL_SCALAR_FUNCTION',

  'SQL_INLINE_TABLE_VALUED_FUNCTION',

  'SQL_TABLE_VALUED_FUNCTION',

  'SQL_STORED_PROCEDURE','VIEW')

  ORDER BY create_date DESC

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 21, 2011 at 6:35 am

  #1424186

  waseem.shahzad 45937 (12/21/2011)

  ---

  Can you put a Date check on this?

  of course, but what do you want to check?> just add a WHERe statement that validates teh create_date column to whateever the date check is supposed to do:

  WHERE create_date > DATEADD(dd,-2,getdate()) --created in last 48 hours?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• waseem.shahzad 45937

  SSC Eights!

  Points: 853

  More actions

  December 21, 2011 at 7:55 am

  #1424254

  Great:

  Thanks

  After a little ammendment this look like:<Working fine with me>

  SELECT

  schema_name(schema_id) As SchemaName,

  name as ObjectName,

  create_date, modify_Date

  FROM sys.objects

  WHERE

  (

  modify_Date >= Cast(Cast((getdate()-0.5) As BigInt) As DateTime)

  Or Create_Date >= Cast(Cast((getdate()-0.5) As BigInt) As DateTime)

  )

  and type_desc IN(

  'SQL_STORED_PROCEDURE', 'VIEW'

  )

  ORDER BY modify_date, create_date DESC

  -- select distinct type_desc from sys.objects