How do you find the last time a database was accessed?

To expand on James Allen’s answer: SELECT, last_user_seek = MAX(last_user_seek), last_user_scan = MAX(last_user_scan), last_user_lookup = MAX(last_user_lookup), last_user_update = MAX(last_user_update) FROM sys.dm_db_index_usage_stats AS i JOIN sys.databases AS d ON i.database_id=d.database_id GROUP BY Use this modified version if you don’t want results per database context and wish to include the database name at the beginning … Read more

Generate a resultset of incrementing dates in T-SQL

If your dates are no more than 2047 days apart: declare @dt datetime, @dtEnd datetime set @dt = getdate() set @dtEnd = dateadd(day, 100, @dt) select dateadd(day, number, @dt) from (select number from master.dbo.spt_values where [type] = ‘P’ ) n where dateadd(day, number, @dt) < @dtEnd I updated my answer after several requests to do … Read more

Using Encrypt=yes in a Sql Server connection string -> “provider: SSL Provider, error: 0 – The certificate’s CN name does not match the passed value.”

Your database connection can be configured to encrypt traffic and to accept any certificate from your server. Not a grand solution, but it worked for me. The resulting connection string should look like this: “[…];Encrypt=True;TrustServerCertificate=True”

Find non-default collation on columns for all tables in SQL Server

Try this script here: DECLARE @DatabaseCollation VARCHAR(100) SELECT @DatabaseCollation = collation_name FROM sys.databases WHERE database_id = DB_ID() SELECT @DatabaseCollation ‘Default database collation’ SELECT t.Name ‘Table Name’, ‘Col Name’, ‘Type Name’, c.max_length, c.collation_name, c.is_nullable FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id WHERE … Read more