Compress All Tables in Database

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.

SELECT DISTINCT D.name [SchemaName]
     , C.name [TableName]
     , A.data_compression_desc
  FROM sys.objects C WITH (NOLOCK)
 INNER JOIN sys.partitions A WITH (NOLOCK) ON C.[object_id] = A.[object_id]
 INNER JOIN sys.schemas D WITH (NOLOCK) ON C.[schema_id] = D.[schema_id]
 WHERE A.data_compression = 0
   AND C.[type] = 'U'
   AND SCHEMA_NAME(C.schema_id) <> 'SYS'
 ORDER BY D.name, C.name
GO

The script below will take care of generating the T-SQL commands necessary to compress all tables that are not already compressed on a database. This script will handle heap tables, clustered indexes and non-clustered indexes. Make sure to output the result to text (CTRL+T) so you can copy, paste and execute in a new window.

DECLARE @TSQL nvarchar(1000)
      , @schema varchar(128)
      , @tablename varchar(128)
      , @type tinyint
      , @indexname varchar(128)

DECLARE C CURSOR
    FOR SELECT DISTINCT D.name [SchemaName]
             , C.name [TableName]
             , I.[name] [IndexName]
             , I.[type]
          FROM sys.objects C WITH (NOLOCK)
         INNER JOIN sys.partitions A WITH (NOLOCK) ON C.[object_id] = A.[object_id]
         INNER JOIN sys.schemas D WITH (NOLOCK) ON C.[schema_id] = D.[schema_id]
         INNER JOIN sys.database_principals E WITH (NOLOCK) ON D.principal_id = E.principal_id
         INNER JOIN sys.indexes I WITH (NOLOCK) ON A.index_id = I.index_id
           AND A.object_id = I.object_id
         WHERE A.data_compression = 0
           AND C.[type] = 'U'
           AND C.[name] NOT LIKE 'MS%'
           AND I.object_id > 1000
           AND SCHEMA_NAME(C.schema_id) <> 'SYS'
         ORDER BY D.name, C.name

   OPEN C
  FETCH NEXT
   FROM C
   INTO @schema, @tablename, @indexname, @type

  WHILE @@FETCH_STATUS = 0
        BEGIN
        SELECT @TSQL = CASE @type
		               WHEN 1 THEN N'ALTER TABLE ['+ @schema +'].['+ @tablename +'] REBUILD WITH (DATA_COMPRESSION = PAGE)' + CHAR(10) +'GO'+ CHAR(10)
                       WHEN 0 THEN N'ALTER TABLE ['+ @schema +'].['+ @tablename +'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' + CHAR(10) +'GO'+ CHAR(10)
                       ELSE N'ALTER INDEX ['+ @indexname +'] ON ['+ @schema +'].['+ @tablename +'] REBUILD WITH (DATA_COMPRESSION = PAGE)' + CHAR(10) +'GO'+ CHAR(10) END
 
        SELECT @TSQL

        FETCH NEXT FROM C INTO @schema, @tablename, @indexname, @type
    END
  CLOSE C
DEALLOCATE C
GO

Setting SQL Alias with PowerShell

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 SQL aliases is saved in the registry under two locations. This first one is for 32bit clients HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\ConnectTo
And this second one is for 64bit clients HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

PowerShell to the rescue! I wrote the Set-SQLAlias function that will take the SQL instance name, port number and alias I want to create. It will use this information to add an entry on the registry or modify an existing alias. I have added this function to my profile so it is always available. THIS FUNCTION MUST BE EXECUTED IN A POWERSHELL SESSION RUNNING UNDER ADMINSTRATOR PRIVILEGES”

Function Set-SqlAlias {
	[Cmdletbinding()] 
	param ( [parameter(Mandatory=$true)] [string] $SqlInstanceName,
            [parameter(Mandatory=$true)] [string] $Alias,
			[parameter(Mandatory=$true)] [string] $Port,
			[parameter(Mandatory=$false)] $OverWrite = 0
          ) 

	BEGIN {
		$x64 = "HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"

		if (!(Test-Path "$x64")) {
			New-Item -Path "$x64" -Name "ConnectTo" 
		}
	}

	PROCESS {
		if ($OverWrite -ne 0) {
			Get-Item -path "$x64"|Remove-ItemProperty -Name "$Alias"|Out-Null 
		} 
		
		if (!(Get-Item -Path "$x64").GetValue("$Alias")) {
			New-ItemProperty -Path "$x64" -Name "$Alias" `
-PropertyType String `
-Value $("DBMSSOCN,{0},{1}" -f "$SqlInstanceName", $Port) `
|Out-Null
		} 
	} 
}

Thanks to my friend Rodney Starcher for helping me out with the PowerShell script.

Cómo Cambiar el Propietario de Una Base de Datos

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 generado por el cmdlet Get-SqlDatabase para encontrar las bases de datos donde la propiedad “.owner” no es “sa”. De ahi, ejecuto el método “.SetOwner” y el “.Alter” por cada una de las bases de datos encontradas para asignar el nuevo propietario “sa”.

Get-SqlDatabase -ServerInstance 'TUIRA' `
| Where {$_.Owner -ne "sa"} `
| foreach {$_.SetOwner("sa"); $_.Alter()}

En Inglés

Change Database Ownership

PROBLEM: Database ownership is set to the user who created or restored the database and I want to change it to “SA”.

SOLUTION: I use the Get-SqlDatabase cmdlet from SqlServer module to capture the list of databases from the SQL instance. I then pipe and search for databases where the “.owner” property is not “sa”. Then pipe the database object to change the “.owner” property to “sa” by invoking the methods “.SetOwner” and “.Alter”.

Get-SqlDatabase -ServerInstance "TUIRA" `
| WHERE {$_.owner -ne 'sa'} `
| foreach {$_.SetOwner('sa'); $_.Alter()}

In Spanish

Table Variables 101

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. I have found in several places on the web people making statements such as temporary tables exist in tempdb and table variables exist in memory. Another reason why we must do our homework and conduct our own tests.

DECLARE @t TABLE (
        RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int)

 SELECT *
   FROM tempdb.sys.objects
  WHERE [type] = 'U'

INSERT INTO @t (
       RequiredDate
     , ShippedDate
     , CustomerID)
VALUES ('20120122', '20120114',4)
     , ('20120122', '20120114',1)
     , ('20120122', '20120114',5);

SELECT * 
  FROM @t;

You can use the IDENTITY column property

DECLARE @t TABLE (
        OrderID int IDENTITY(1,1)
      , RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int)

INSERT INTO @t (
       RequiredDate
     , ShippedDate
     , CustomerID)
VALUES ('20120122', '20120114',4)
     , ('20120122', '20120114',1)
     , ('20120122', '20120114',5);

SELECT *
  FROM @t;

You can define a primary key

DECLARE @t TABLE (
        OrderID int IDENTITY(1,1) PRIMARY KEY
      , RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int)
 
INSERT INTO @t (
       RequiredDate
     , ShippedDate
     , CustomerID)
VALUES ('20120122', '20120114',4)
     , ('20120122', '20120114',1)
     , ('20120122', '20120114',5);
 
SELECT * FROM @t;

You can define a clustered key other than the primary key.

DECLARE @t TABLE (
        OrderID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
      , RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int,
 UNIQUE CLUSTERED (CustomerID, OrderID))

You can create a check constraint

DECLARE @t TABLE (
        OrderID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
      , RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int,
 UNIQUE CLUSTERED (CustomerID, OrderID),
 CHECK (RequiredDate BETWEEN '20131001' AND '20131031'))

Now, just because you can do all these things with table variable you shouldn’t go and define every table variable with all these properties. You should always practice good database design.

How to Find Out if Service Broker is Enabled on a Database

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.

SELECT name [DatabaseName]
     , is_broker_enabled [IsServiceBrokerEnabled]
     , is_master_key_encrypted_by_server [HasDatabaseMasterKey]
  FROM sys.databases
 WHERE is_broker_enabled = 1

How to Find a Database User in Your SQL Environment

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 of all SQL servers in my environment to then proceed and loop through my list of servers and databases to look for the pattern of the user name or login, in this case “syoung”.

Notice on line 2 how the value assigned to the variable is surrounded by asterisks. This will support the wildcard search on line 18 for any database account with extra characters before or after the string value.

On line 5 and 6 is the query executed against my Central Management Server to get the list of registered SQL Servers in my environment.

CLEAR
[string] $sDBUser = "*young*"

$sSQL = @"
SELECT DISTINCT [server_name]
  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
 WHERE [server_name] NOT LIKE '%.%'
"@

$aSQLServers = Invoke-Sqlcmd -ServerInstance "TUIRA" `
               -Database "msdb" `
               -Query $sSQL

foreach ($sSQLServer in $aSQLServers) {
    $sSQLServer = $sSQLServer.server_name
	  Get-SqlDatabase -sqlserver "$sSQLServer"| `
        % {Get-SqlUser $_}| `
        WHERE {$_.Name -like "$sDBUser"}| `
        SELECT Server, dbname, name
}

How Do You Remove Logins from Your SQL Environment?

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 you can help me enhance it with some recommendations. The most recent update handles the transfer of schemas to dbo because a database user can not be dropped if it owns a schema.

In the absence o f a SQL Server 2008 instance, you can use the SQLCMD utility to execute the included script against all your servers; either way you will need to provide a list of servers.

I recommend you build a server for the sole purpose of collecting information about all SQL servers in your environment, use this server as your “Registered Central Management Server”. Once you have your Central Management Server ready and with all your SQL Servers registered, right click on the node or group of servers you want to check for the existence of the login to be removed and select “New Query”. After a second or two you will notice a query window with a pink status bar. Copy and paste the code below into that window. On the query menu, click Specify Values for Template Parameters and change the value with the login you are looking for.

SELECT [name] 
  FROM master..syslogins
 WHERE [name] LIKE '%&lt;login_name, sysname, login_name&gt;%'

If the query above did not return any records, there is nothing else to do. If records were returned, you will see the name of the server(s) where the login exists and the execution of the script below will take care of removing them from your environment.

DECLARE @dbname VARCHAR(100),
        @USER VARCHAR(100),
        @login VARCHAR(100),
        @SQL NVARCHAR(2000),
        @version TINYINT,
        @CATALOGNAME nvarchar(128),
        @SCHEMANAME nvarchar(128),
        @OBJECTTYPE nvarchar(20),
        @OBJECTNAME nvarchar(128)

SET NOCOUNT ON

--[ GET SQL SERVER VERSION.  WILL USE FOR DML COMMANDS NOT COMPATIBLE IN ALL VERSIONS ]
SELECT @version = LEFT(CAST(SERVERPROPERTY('productversion') AS varchar(50)),1)

SET @USER = '&lt;login_name, sysname, login_name&gt;'

IF OBJECT_ID('tempdb.dbo.##users') IS NULL
   CREATE TABLE ##users ([USER] VARCHAR(100))

IF OBJECT_ID('tempdb.dbo.##Schemas') IS NULL
CREATE TABLE ##Schemas (
       ID int IDENTITY(1,1),
       CATALOGNAME nvarchar(128),
       SCHEMANAME nvarchar(128),
       OBJECTNAME nvarchar(128),
       OBJECTTYPE nvarchar(20))

--[ COLLECT NAME OF ALL DATABASES ON THE SERVER ]
DECLARE crDBname CURSOR FOR
 SELECT [name]
   FROM master.dbo.sysdatabases
  WHERE databasepropertyex([name],'Updateability') = 'READ_WRITE' AND
        databasepropertyex([name], 'status') = 'ONLINE' 
  ORDER BY [name]

--[ COLLECT NAME OF ALL SERVER LOGINS THAT MATCH PATTERN OF LOGIN TO REMOVE ]
DECLARE crLogin CURSOR FOR
 SELECT loginname
   FROM master.dbo.syslogins
  WHERE [name] LIKE '%'+ @USER

--[ REPOSITORY OF ALL DATABASE USERS. IT IS EMPTY INITIALLY ]
DECLARE crUser CURSOR FOR
 SELECT [USER]
   FROM ##users

--[ CHECK FOR THE EXISTANCE OF USER IN EVERY DATABASE ]
   OPEN crDBname
  FETCH NEXT FROM crDBname INTO @dbname
  WHILE @@fetch_status = 0
        BEGIN
        DELETE FROM ##users

        SET @SQL = N'IF EXISTS (SELECT * FROM ['+ @dbname +'].dbo.sysusers WHERE name LIKE ''%'+@USER+''')' +CHAR(13)+
                    '   BEGIN' + CHAR(13) +
                    '   INSERT INTO ##users' + CHAR(13) +
                    '   SELECT name FROM ['+ @dbname +'].dbo.sysusers WHERE name LIKE ''%'+ @USER +''''+CHAR(13) +
                    '   PRINT ''USER '+ @USER +' EXISTS IN '+ @dbname +'.''' + CHAR(13) +
                    'END'
        EXEC (@SQL)

        --[ CHECK FOR DB OBJECT OWNERSHIP BY THIS USER &amp; TRANSFER OWNERSHIP TO DBO IF ]
        --[ EXISTS. ONLY APPLIES TO SQL 2005+                                         ]
        IF @version IN(9,0) --[ CHECK FOR SQL VERSION &gt;= 2005                 ]
           BEGIN
           SELECT @SQL = N'USE ' + @dbname + CHAR(13) +
                          'INSERT ##Schemas (CATALOGNAME, SCHEMANAME, OBJECTNAME, OBJECTTYPE)'+CHAR(13)+
                          'SELECT [TABLE_CATALOG] [CATALOGNAME], '+CHAR(13)+
                          '       [TABLE_SCHEMA] [SCHEMANAME], '+CHAR(13)+
                          '       [TABLE_NAME] [OBJECTNAME], '+CHAR(13)+
                          '       ''VIEW'' [OBJECTTYPE] '+CHAR(13)+
                          '  FROM INFORMATION_SCHEMA.SCHEMATA A '+CHAR(13)+
                          ' INNER JOIN INFORMATION_SCHEMA.VIEWS B ON A.[SCHEMA_NAME] = B.TABLE_SCHEMA '+CHAR(13)+
                          ' WHERE SCHEMA_OWNER LIKE ''%'+ @USER +''''+CHAR(13)
           EXEC (@SQL) 

           SELECT @SQL = N'USE ' + @dbname + CHAR(13) +
                          'INSERT ##Schemas (CATALOGNAME, SCHEMANAME, OBJECTNAME, OBJECTTYPE)'+CHAR(13)+
                          'SELECT [SPECIFIC_CATALOG] [CATALOGNAME], '+CHAR(13)+
                          '       [SPECIFIC_SCHEMA] [SCHEMANAME], '+CHAR(13)+
                          '       SPECIFIC_NAME [OBJECTNAME], '+CHAR(13)+
                          '       ROUTINE_TYPE [OBJECTTYPE] '+CHAR(13)+
                          '  FROM INFORMATION_SCHEMA.SCHEMATA A '+CHAR(13)+
                          ' INNER JOIN INFORMATION_SCHEMA.ROUTINES B ON A.[SCHEMA_NAME] = B.SPECIFIC_SCHEMA '+CHAR(13)+
                          ' WHERE SCHEMA_OWNER LIKE ''%'+ @USER +''''+CHAR(13)
            EXEC (@SQL)

           SELECT @SQL = N'USE ' + @dbname + CHAR(13) +
                          'INSERT ##Schemas (CATALOGNAME, SCHEMANAME, OBJECTNAME, OBJECTTYPE)'+CHAR(13)+
                          'SELECT CATALOG_NAME [CATALOGNAME], '+CHAR(13)+
                          '       [SCHEMA_NAME] [SCHEMANAME], '+CHAR(13)+
                          '       TABLE_NAME [OBJECTNAME], '+CHAR(13)+
                          '       ''TABLE'' [OBJECTTYPE] '+CHAR(13)+
                          '  FROM INFORMATION_SCHEMA.SCHEMATA A '+CHAR(13)+
                          ' INNER JOIN INFORMATION_SCHEMA.TABLES B ON A.[SCHEMA_NAME] = B.TABLE_SCHEMA '+CHAR(13)+
                          ' WHERE SCHEMA_OWNER LIKE ''%'+ @USER +''''+CHAR(13)
           EXEC (@SQL)

           DECLARE crSchema CURSOR FOR
            SELECT [CATALOGNAME], [SCHEMANAME], [OBJECTTYPE], [OBJECTNAME]
              FROM ##Schemas
             ORDER BY ID

            OPEN crSchema
           FETCH NEXT FROM crSchema INTO @CATALOGNAME, @SCHEMANAME, @OBJECTTYPE, @OBJECTNAME
           WHILE @@FETCH_STATUS = 0
                 BEGIN /*[ TRANSFER DB OBJECTS FROM USER SCHEMA TO DBO SCHEMA ]*/
                 SET @SQL = N'USE ' + @dbname + CHAR(13) +
                             'ALTER SCHEMA [dbo] TRANSFER ['+ @SCHEMANAME +'].['+ @OBJECTNAME +']'+CHAR(13)+
                             'PRINT ''Transfering ownership of '+ @OBJECTTYPE +' '+ @OBJECTNAME +' to [dbo]'''
                 EXEC (@SQL)

                 FETCH NEXT FROM crSchema INTO @CATALOGNAME, @SCHEMANAME, @OBJECTTYPE, @OBJECTNAME
             END       /*[ TRANSFER DB OBJECTS FROM USER SCHEMA TO DBO SCHEMA ]*/
           CLOSE crSchema
           DEALLOCATE crSchema
        END
TRUNCATE TABLE ##Schemas

        --[ CREATE DDL STATEMENT TO REMOVE USER.  HERE WE USE THE @version    ]
        --[ VARIABLE TO DETERMINE WHICH COMMAND TO USE                        ]
        OPEN crUser
        FETCH NEXT FROM crUser INTO @USER
        WHILE @@fetch_status = 0
              BEGIN
              IF @version IN(9,0)
                 BEGIN
                 SET @SQL = N'USE ' + @dbname + CHAR(13) +
                             'ALTER USER ['+ @USER +'] WITH DEFAULT_SCHEMA = [dbo]'+ CHAR(13) +
                             'IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'''+ @USER +''')' + CHAR(13) +
                             '    DROP SCHEMA ['+ @USER +']' + CHAR(13) +
                             'IF EXISTS (SELECT * FROM sysusers WHERE name = '''+ @USER +''')' + CHAR(13) +
                             '   BEGIN' + CHAR(13) +
                             '   DROP USER ['+ @USER +']' + CHAR(13) +
                             'PRINT ''USER '+ @USER +' has been dropped FROM '+ @dbname +'.''' + CHAR(13) +
                             'END'
              END
              ELSE
                 BEGIN
                 SET @SQL = N'USE ' + @dbname + CHAR(13) +
                             'IF EXISTS (SELECT * FROM sysusers WHERE name = '''+@USER+''')' + CHAR(13) +
                             '   BEGIN' + CHAR(13) +
                             '   EXEC sp_revokedbaccess '''+@USER+'''' + CHAR(13) +
                             'PRINT ''USER '+@USER+' has been dropped FROM '+@dbname+'.''' + CHAR(13) +
                             'END'
              END --IF
              EXEC (@SQL)

              FETCH NEXT FROM crUser INTO @USER
          END --WHILE
        CLOSE crUser
        FETCH NEXT FROM crDBname INTO @dbname
    END

   DEALLOCATE crUser
     CLOSE crDBname
DEALLOCATE crDBname

DROP TABLE ##users
SET @SQL = ''

OPEN crLogin
FETCH NEXT FROM crLogin INTO @login
--PRINT 'Logins = ' +@login
WHILE @@fetch_status = 0
      BEGIN
      IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = @login)
         SET @SQL = N'EXEC master.dbo.sp_droplogin ['+ @login +']'
         EXEC (@SQL)

      IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = @login )
         SET @SQL = N'USE master' +CHAR(13) +
                     'EXEC master.dbo.sp_revokelogin '''+ @login +'''' + CHAR(13)
         EXEC (@SQL)
      FETCH NEXT FROM crLogin INTO @login
  END
CLOSE crLogin
DEALLOCATE crLogin

How to Copy All Tables Definition with PowerShell

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 that access MS SQL Server –I’m a fan, what else can I say.

There are a few highlighted lines of code I want to mention because I had a bit of trouble while scripting tables with dependencies on user defined data types and partitioned tables. Line 5 and 6 are string variables assigments for the target server and database. On line 13, I added the scripting option “NoFileGroup” because the source database was set up using multiple filegroups and the script failed when these file groups were not available on the target server; the target server in this example will have a single file group. On line 15, I added the “WithDependencies” which helped with the creation of partitioned table functions and schemas. And on line 16, I used the “IncludeIfNotExists” in order to avoid errors if the database objects already exists.

CLEAR 

$sSRCServer = "TUIRADEVELOPMENT" 
$sSRCDatabase = "AdventureWorks" 
$sTRGServer = "TUIRA" 
$sTRGDatabase = "AdventureWorks" 

$oSO = New-SqlScriptingOptions 
$oSO.Permissions = $false 
$oSO.ClusteredIndexes = $true 
$oSO.Indexes = $true 
$oSO.Triggers = $true 
$oSO.NoFileGroup = $true
$oSO.NonClusteredIndexes = $true 
$oSO.WithDependencies = $true
$oSO.IncludeIfNotExists = $true

$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable 

foreach ($oTable in $oTables){  
    $oT = $oTable.Name  
    $oS = $oTable.Schema  
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO 
    invoke-sqlcmd -ServerInstance "$sTRGServer" -Database "$sTRGDatabase" `
          -Query "$DDL" 
}

This solution can easily be converted to a function with optional parameters that map to scripting options. The target variables for server and/or database could hold arrays for copying the tables definition to multiple servers. A form could also be developed using WPF to make it user friendly. What do you think?

Cómo Copiar el Esquema de Tablas Utilizando PowerShell

PROBLEMA: Necesitas copiar el esquema de tablas de un servidor Microsoft SQL a otro servidor SQL.

SOLUCION: Para resolver este problema utilizo PowerShell y el SQLPSX. Si sigues este blog, podrás notar que utilizo la extensión SQLPSX de SQL para PowerShell en todos mis PowerShell scripts de base de datos.

Hay algunas líneas en el código adjunto las cuales hago resaltar porque al escribir el script tuve ciertas dificultades ya que existen dependencias entre las tablas y tipos definidos por el usuario además de la existencia de tablas con particiones. La línea 5 y 6 son para asignar el nombre del servidor y base de datos de destino a variables. En la línea 13 agrego la opción de scripting “NoFileGroup” ya que la base de datos de origen está configurada con múltiples grupos de archivos (filegroups) y mi script termino en error cuando estos filegroups no se encontraron en la base de datos de destino. En este ejemplo, la base de datos de destino cuenta con un solo filegroup. En la línea 15 añado la opción de scripting “WithDependencies” para ayudar con la creación de funciones y esquemas de particiones de tablas. Finalmente en la línea 16, incluyo la opción de scripting “IncludeIfNotExists” para verificar que los objetos de base de datos a crear no existan en la base de datos de destino y así evitar los errores de creación.

CLEAR

$sSRCServer = "TUIRADEVELOPMENT"
$sSRCDatabase = "AdventureWorks"
$sTRGServer = "TUIRA"
$sTRGDatabase = "AdventureWorks"

$oSO = New-SqlScriptingOptions
$oSO.Permissions = $false
$oSO.ClusteredIndexes = $true
$oSO.Indexes = $true
$oSO.Triggers = $true
$oSO.NoFileGroup = $true
$oSO.NonClusteredIndexes = $true
$oSO.WithDependencies = $true
$oSO.IncludeIfNotExists = $true

$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Name
    $oS = $oTable.Schema
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO
    invoke-sqlcmd -ServerInstance "$sTRGServer" -Database "$sTRGDatabase" `
         -Query "$DDL"
}

Esta solución puede ser convertida a una función de PowerShell y utilizar parámetros para vincular las opciones de scripting. Las variables de servidor y base de datos de destino pueden contener una matriz (array) con nombres de servidores y base de datos para así copiar el esquema de tablas a múltiples servidores. Un formulario web puede ser creado utilizando WPF para facilitar el uso de esta solución. ¿Qué te parece esta solución?

Tag: DBA