Tag: SQL

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

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
}
Related Links

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?

Related Links

En Español

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?

Artículos Relacionados

In English

How to Review SQL Logs with PowerShell

PROBLEM: You need an easy way to review the MS SQL Server logs of all the servers you manage.

SOLUTION: Use the Get-SqlErrorLog function from the SQL Server PowerShell Extensions (SQLPSX).

I will show you some of the things you can achieve with the use of the Get-SqlErrorLog function and a bit of creativity.  We will start by retrieving  the content of the current error log of our server “TUIRA” by executing:

Get-SQLErrorLog –SQLServer "TUIRA"

In the next example we want to retrieve the content of log file number 4 by specifying the optional parameter lognumber of the Get-SqlErrorLog function.

Get-SQLErrorLog –SQLServer "TUIRA" –lognumber 3

Now, getting the entire error log of a SQL server is not very useful, specially from a busy server.  When I’m looking into the error logs I do so with a purpose, most of the time is because I’m looking for errors or something specific.  In this following example, we’ll apply a filter and display entries where the word “error” exist in the message.

[string] $sSQLServerName = "TUIRA"
Get-SQLErrorLog –SQLServer $sSQLServerName|WHERE {$_.Text `
    –match "error"}|Format-Table -AutoSize

If you get in the habit of checking these logs on a daily basis, you can filter your result a bit further by just retrieving the logs starting from the previous day.  Here is an example of how to accomplish that.

[string] $sSQLServerName = "TUIRA"
$a = Get-Date
$d = $a.day – 1
$m = $a.month
$y = $a.year
$dDate = "$m/$d/$y"

Get-SQLErrorLog –SQLServer $sSQLServerName|WHERE {$_.Text `
    –match "error" -and $_.LogDate –ge $dDate}|Format-Table -AutoSize

And our last example includes an array with a list of SQL server names. We’ll loop through this array and retrieve error entries from their log files.

$a = Get-Date
$d = $a.day – 1
$m = $a.month
$y = $a.year
$dDate = "$m/$d/$y"

$aSQLServers = "SQLServer1","SQLServer2","SQLServer3","SQLServer4"

foreach ($sSQLServer in $aSQLServers) {
    $sSQLServer
    Get-SQLErrorLog –SQLServer $sSQLServer|WHERE {$_.Text `
        -match "error" -and $_.LogDate –ge $dDate}|Format-Table `
        –AutoSize}

As a DBA, part of my morning routine is to review error logs from the SQL servers I manage. In my production environment, I have a job that retrieves the list of production servers from an inventory database (iSQLPS) and generate an HTML report for each server. So by the time I get to my desk with my first cup of java for the day, I have the reports waiting.
Do not forget to download the SQL Server PowerShell Extensions from Codeplex.com

How to Script All Stored Procedures From a Database with PowerShell

PROBLEM: You need to script all stored procedures from a database.  Each stored procedure needs to be on its own file so you can add them to a source control repository.

SOLUTION: The solution here is a variation of what we did for “How to Script All Tables From a Database with PowerShell”.  The only difference in this solution is the use of the Get-SQLStoredProcedure function from the SQL Server PowerShell Extensions (SQLPSX).

CLEAR
$sServer = "TUIRA"
$sDatabase = "AdventureWorks"

$oSO = New-SqlScriptingOptions
$oSO.Permissions = $true
$oProcs = Get-SqlDatabase $sServer $sDatabase|Get-SqlStoredProcedure

foreach ($oProc in $oProcs){
    $oP = $oProc.Name
    $oS = $oProc.Schema
    $DDL = Get-SqlScripter $oProc -scriptingOptions $oSO
    Out-File -FilePath "E:AdventureWorks$oS`_$oP.sql" -InputObject $DDL
}
Related Links
  • How to Script All Tables From a Database with PowerShell.

    • How to Script All Tables From a Database with PowerShell

    PROBLEM: You need to script all the tables from a database.  Each table needs to be on its own file so they can be added to a source control repository.

    SOLUTION: The use of SQL Server PowerShell Extensions (SQLPSX) facilitates the solution I’m about to show. The first example is a straight to the point, just DDL for the table without indexes, permissions, or any other scripting options. I used the Get-SQLDatabase, Get-SQLTable and Get-SQLScripter functions from SQLPSX to script out each table from the database into their own file.

    CLEAR
$sServer = "TUIRA" 
$sDatabase = "AdventureWorks"
$oTables = Get-SqlDatabase $sServer $sDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Name
    $oS = $oTable.Schema
    $DDL = Get-SqlScripter $oTable
    Out-File -FilePath "E:AdventureWorks$oS`_$oT.sql" `
        -InputObject $DDL
}

    Next, I extended the initial script by adding a reference to the SMO scripting class SQLScriptionOptions.  Now, I can script all primary key, indexes, foreign keys, default values and more.  Look for “ScriptingOptions Members” in Books Online or the Internet and you will find under public properties all the possible things you can include or exclude from your DDL scripts.

    Here, I chose to NOT include permissions and to include the clustered index, regular indexes, triggers, and schemas.

    CLEAR
$sServer = "TUIRA"
$sDatabase = "AdventureWorks"

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

$oTables = Get-SqlDatabase $sServer $sDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Name
    $oS = $oTable.Schema
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO
    Out-File -FilePath "E:AdventureWorks$oS`_$oT.sql" `
        -InputObject $DDL
}