Backup all databases

Problem Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to […]

How to hide SQL Server user databases in SQL Server Management Studio

Please note this is copy paste from https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/ And my personal experience is that it doesn’t work for MSSMS Problem I have a SQL Server instance that has hundreds of databases.  Navigating the database tree […]

SQL Server – How to get sizes of all databases on a server

To get database size information we can use sys.master_files catalog view. This view contains a row per file of a database for all databases. The columns of interest for retrieving database size information are: Column Name Description […]

Delete oldest duplicate in SQL

I’ve got a table that has rows that are unique except for one value in one column (let’s call it ‘Name’). Another column is ‘Date’ which is the date it was added to the database. […]

Trigger on multiple insert

I had a case where I needed to insert multiple rows in a single query. A trigger is only executed once per insert, hence I only got it to trigger on a single record. The […]

Remove MUST_CHANGE_PASSWORD from user

If you accidentially forget to remove the “Must change password” and the “Enforce password policy” when you create a user, you need to change the password for the user. This is a must before you […]

List columns in a table

This will list all columns in alphabetic order SELECT c.name ‘Column Name’, t.Name ‘Data type’, c.max_length ‘Max Length’, c.precision , c.scale , c.is_nullable, ISNULL(i.is_primary_key, 0) ‘Primary Key’ FROM sys.columns c INNER JOIN sys.types t ON […]

MSSQL Recover users after restore

Save the below script into a stored procedure, and you can call this after restoring a database, but you can also just copy this into a query. Don’t forget that the query should be using […]

Database last accessed

— Get Last Restart time SELECT crdate FROM sysdatabases WHERE name = ‘tempdb’ go — get last db access time (Null = no access since last reboot) SELECT name, last_access =(select X1= max(LA.xx) from ( […]