checkdbcc date for all database

Question

checkdbcc date for all database

lan2022nguyen

SSC-Addicted

Points: 459

October 4, 2023 at 6:57 pm

CREATE TABLE #tempdblogin 
  (
  
  lastcheck nvarchar(100)
  )

EXEC sp_MSforeachdb 'USE ? insert into #tempdblogin SELECT  DATABASEPROPERTYEX(DB_NAME(), "LastGoodCheckDbTime")  ' 





SELECT * FROM #tempdblogin



DROP table #tempdblogin

getting the following error:

Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135506 More actions · Answer 1

... convert( desired_date_type, DATABASEPROPERTYEX(DB_NAME(), "LastGoodCheckDbTime")) ...

I've modified your script a little bit to be more informative :

CREATE TABLE #lastcheckdb 
  (
  DbName sysname,
  Updateability sysname,
  lastcheck datetime2(0) null
  )

EXEC sp_MSforeachdb 'USE [?]; insert into #lastcheckdb SELECT db_name() as DbName, convert(sysname, DATABASEPROPERTYEX(DB_NAME(), ''Updateability'')) as Updateability,  convert(datetime2(0),DATABASEPROPERTYEX(DB_NAME(), "LastGoodCheckDbTime"), 100) as lastcheck' 

SELECT * 
FROM #lastcheckdb
order by DbName; 
DROP table #lastcheckdb ;