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. […]
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 ( […]
Insert huge SQL script
http://www.diaryofaninja.com/blog/2009/09/25/running-large-mssql-scripts-amp–resulting-memory-errors It took time to get it to work, I went through the same headaches, but finally got it running with below command, including a login. https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility sqlcmd -S YOURSQLSERVER\INSTANCENAME -i “C:\Your Script.sql” sqlcmd -a […]
With q as query
Its cool to use subqueries, but it can be even cooler if you want to take data from a result and handle that in different way. And can be used instead of creating a temp […]