Category: MS SQL

PROBLEM: I need to find out all the tables in a database that are not using data compression. SOLUTION: Here is the T-SQL code to list all tables in a database that are not compressed. The script below will take care of generating the T-SQL commands necessary to compress all tables that are not already […]

PROBLEM: The SQL Servers instances I manage have long cryptic names that are almost impossible to memorize and too many keystrokes to type; besides, I rather use my memory for other things. How can I set up aliases for all the SQL instances I manage in a quick an easy manner? SOLUTION: The configuration of […]

PROBLEMA: Tengo base de datos donde el propietario no es “sa”, cómo puedo cambiar el propietario de varias bases de datos a “sa” de una forma dinámica. SOLUCION: Yo utilizo el cmdlet Get-SqlDatabase del módulo SqlServer para capturar las bases de datos de una instancia de SQL Server. Entonces hago una búsqueda en el objeto […]

Table variables fall in one of those areas where lack of knowledge and understanding can get you in trouble. I’m leaving the creative part of getting in trouble up to you and here, I will show you a few things I didn’t know about table variables. Table variables exist in tempdb regardless of their size. […]

PROBLEM: I need to know if a database has Service Broker enabled. SOLUTION: The query I have listed below will provide you with the information you are looking for and if a database master key has been created for a database.

PROBLEM: Sal Young is a member of your DBA team and today is his last day at work. You need to find out if his login is mapped to any database throughout your MS SQL environment. SOLUTION: I used Central Management Server, PowerShell, and SQLPSX. I queried my Central Management Server to get the name […]

PROBLEM: I need to change the owner of several SQL Server Agent Jobs. Is there an easy way to do it? SOLUTION: Use PowerShell and SQLPSX. I use the Get-AgentJob function from SQL Server PowerShell Extension (SQLPSX) to find the jobs I need to modify. I need to change ownership of all SQL Server Agent […]

PROBLEM: I need a T-SQL script to remove a login from all SQL Servers in my environment. SOLUTION: My solution includes the use of MS SQL 2008 Central Management Servers and the script listed below. This script is a work in progress because I’m constantly adding, removing and updating features as I encounter problems. Maybe […]

PROBLEM: You need to copy the table definition for all tables in a database between two SQL servers. SOLUTION: One of the requirements is to use PowerShell and I will use it in combination with SQLPSX. Yes, I will use SQLPSX again and you probably have noticed I use it in all my PowerShell scripts […]