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

DB_RecentFileGrowth

IF (SELECT CONVERT(int,value_in_use) FROM sys.configurations WHERE [name] = ‘default trace enabled’ ) = 1
BEGIN

DECLARE @curr_tracefilename varchar(500)
, @base_tracefilename varchar(500)
, @indx int;

SELECT @curr_tracefilename = [path] FROM sys.traces where is_default = 1 ;

SET @curr_tracefilename = REVERSE(@curr_tracefilename);

SELECT @indx = PATINDEX(‘%\%’, @curr_tracefilename) ;

SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) – @indx) + ‘\log.trc’ ;

SELECT (DENSE_RANK() OVER(ORDER BY StartTime DESC))%2 AS [l1] , CONVERT(int, EventClass) [EventClass] , DatabaseName
, [Filename] , StartTime
, EndTime
, (IntegerData*8.0/1024) [ChangeInSize] FROM ::fn_trace_gettable( @base_tracefilename, default )
WHERE EventClass >= 92
AND EventClass <= 95
ORDER BY StartTime DESC;
END
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

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 To Change the SQL Server Agent Job Owner

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 jobs from the login “THERIVERsyoung” to “sa” on the server “TUIRA”. This first script will display all jobs where the owner matches the login “THERIVERsyoung”.

CLEAR 

$oSQLAgentJobs = Get-AgentJob -jobserver "TUIRA"

foreach ($job in $oSQLAgentJobs) {
    if ($job.OwnerLoginName -eq "THERIVERsyoung") {
        $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
    }
}

I will proceed to add the code to change the ownership for all the jobs that meet the criteria. I want to recognize Sean McCown (MidnightDBA) for providing a clear explanation on how to change the sql server job owner. I strongly recommend you watch the entire presentation on this link for a good understanding of the Alter and Refresh methods of the JobServer object. On line number 8 I change the job ownership to “sa”. On number 9 I call the Alter method to update the property I changed on line 8 and on line 10 the Refresh method is called to complete the change.

CLEAR 

$oSQLAgentJobs = Get-AgentJob -jobserver "TUIRA"

foreach ($job in $oSQLAgentJobs) {
    if ($job.OwnerLoginName -eq "THERIVERsyoung") {
        $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
        $job.set_OwnerLoginName("sa")
        $job.Alter()
        $job.Refresh()
        $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
    }
}

I decided to add an extra few lines of code to search my entire SQL Server environment for jobs owned by “THERIVERsyoung”. I leveraged the use of Central Management Server to retrieve the name of all SQL Servers by running the query on lines 4 & 5. Notice that on line 8 I use the Invoke-Sqlcmd SQL comandlet to execute the query stored in the variable $sSQL and that I run it against DBACONSOLE which is my Central Management Server.

CLEAR

$sSQL = @"
SELECT [server_name]
  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
"@

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

foreach ($sSQLServer in $aSQLServers) {
    $oSQLAgentJobs = $null

    $sSQLServerName = $sSQLServer.server_name
    $oSQLAgentJobs = Get-AgentJob -jobserver "$sSQLServerName"    

    foreach ($job in $oSQLAgentJobs) {
        if ($job.OwnerLoginName -eq "THERIVERsyoung") {
            $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
            $job.set_OwnerLoginName("sa")
            $job.Alter()
            $job.Refresh()
            $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
        }
    }
}

You could easily turn this solution into a function with parameters for the current login and the new login. The preceding examples show the approach I took when my boss asked me to look for and change all SQL Server Agent jobs I was set as the owner.

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?

Category: MS SQL