Category Archives: T-SQL

T-SQL: Find Out Who Has Locks In Your Databases

Here are a couple of ways to see who is locking up your SQL Server database. First, the undocumented system stored procedure sp_who2 gives you a few more columns than the documented sp_who. And second, this custom query gives a … Continue reading

Posted in T-SQL | Leave a comment

T-SQL: View and Set SQL Server Maximum Memory Usage

Here’s how you can use T-SQL to set the maximum memory to use. In this example, if the server has more than 4GB of RAM, it reduces SQL Server’s usage by 1GB; if it has less than 4GB, it reduces … Continue reading

Posted in T-SQL | Leave a comment

T-SQL: Trailing Spaces And String Comparison

Here’s a useful tidbit regarding trailing spaces and SQL Server queries. Note the count of the results from the query below. Because SQL Server follows the ANSI/ISO SQL-92 specification, the string ‘test’ and ‘test    ‘ (with spaces at the end) are … Continue reading

Posted in T-SQL | Leave a comment

Microsoft SQL Server 2014: Change Default Data, Log, and Backup Directories via T-SQL or PowerShell

If you need to change the default data, log, and/or backup directories on a SQL 2014 MS SQL Server default instance after installation, you can use these three SQL commands: EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’ , N’Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer’ , N’DefaultData’ , … Continue reading

Posted in MS SQL Server, PowerShell, T-SQL | Leave a comment

T-SQL: Count of Tables, Stored Procedures, and Functions in All User Databases

This query uses the undocumented Microsoft stored procedure master.sys.sp_MSforeachdb to iterate over all user databases (user databases always start with IDs > 4) and count the number of tables, stored procedures and functions. You can use this same pattern to … Continue reading

Posted in T-SQL | Leave a comment

T-SQL: Script all Synonyms for a Database

If you have synonyms in a database that point to a database that you need to move or rename, you will need to update those synonyms as part of your update. Here’s a script that generates DROP/CREATE statements for all … Continue reading

Posted in T-SQL | Leave a comment