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 … Read more
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”. … Read more
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 … Read more
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 … Read more
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 … Read more
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, … Read more
PROBLEM: You manage many MS SQL servers and need an easy way to review jobs that have failed. SOLUTION: I will show you one way to accomplish this task with results displaying on the screen or to a file. To make your life easier, I recommend you download & install PowerGUI from Quest Software. This … Read more
PROBLEM: I need an easy way to script out all the tables from a database in MS SQL 2000 and above. SOLUTION: Like anything in programming, there are a thousand ways to skin a cat. Here, I’ll show you the simplest way I found to meet this request. Go ahead and open a new session … Read more
USE: To generate T-SQL code for all indexes and PK constraints from a database. CLEAR $sSRCServer = “TPW520” $sSRCDatabase = “iSQLPS” $oSO = New-SqlScriptingOptions $oSO.ClusteredIndexes = $true $oSO.Indexes = $true $oSO.NonClusteredIndexes = $true $oSO.IncludeIfNotExists = $true $oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable foreach ($oTable in $oTables){ $oT = $oTable.Indexes foreach ($oIndex in $oT) { $DDL = … Read more