Gerando scripts do banco de dados por PowerShell – Passo 2

Publicado em 03/02/2012 por Paulo R. Pereira

Partindo da mesma ideia de utilizar o objeto Scripter do SMO para gerar o scripts do SQL Server por .NET, agora veremos como fazer o mesmo procedimento com PowerShell:

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll")
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll")

# Objetos
$srv = new-object Microsoft.SqlServer.Management.Smo.Server(".\SQLEXPRESS")

$db = $srv.Databases["DemoAspNet"]

# Gerando o script do banco de dados
$script = $db.Script()

$script += "`nUSE [DemoAspNet]`n"

# Relacionando as URNs dos objetos que serão "scriptados"

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$roles =
    $db.Roles | Where { $_.IsFixedRole -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$schemas =
    $db.Schemas | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$tables =
    $db.Tables | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$views =
    $db.Views | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$procs =
    $db.StoredProcedures | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

$urn = new-object Microsoft.SqlServer.Management.Smo.UrnCollection

$urn.AddRange($roles)
$urn.AddRange($schemas)
$urn.AddRange($tables)
$urn.AddRange($views)
$urn.AddRange($procs)

$scr = new-object Microsoft.SqlServer.Management.Smo.Scripter
$scr.Server = $srv
$scr.Options.DriAll = $TRUE

# Gerando o script dos objetos pelo Scripter
$script += $scr.Script($urn)

# Salvando o script gerado em um arquivo
$go = "`nGO`n"

$script | % { $_ + $go} | Out-File "C:\Temp\DemoAspNet.sql"

Forma resumida para ser executado dentro do próprio contexto do SQL Server (Jobs, PowerShell do SQL Server ou sqlps.exe):

# Sem referências

# Objetos
sl SQLSERVER:\SQL\LOCALHOST\SQLEXPRESS

$srv = get-item .

sl Databases\DemoAspNet

$db = get-item .

# Gerando o script do banco de dados
$script = $db.Script()

$script += "`nUSE [DemoAspNet]`n"

# Relacionando as URNs dos objetos que serão "scriptados"

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$roles =
    dir Roles | Where { $_.IsFixedRole -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$schemas =
    dir Schemas | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$tables =
    dir Tables | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$views =
    dir Views | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

[Microsoft.SqlServer.Management.Sdk.Sfc.Urn[]]$procs =
    dir StoredProcedures | Where { $_.IsSystemObject -eq $FALSE } | % { $_.Urn }

$urn = new-object Microsoft.SqlServer.Management.Smo.UrnCollection

$urn.AddRange($roles)
$urn.AddRange($schemas)
$urn.AddRange($tables)
$urn.AddRange($views)
$urn.AddRange($procs)

$scr = new-object Microsoft.SqlServer.Management.Smo.Scripter
$scr.Server = $srv
$scr.Options.DriAll = $TRUE

# Gerando o script dos objetos pelo Scripter
$script += $scr.Script($urn)

# Salvando o script gerado em um arquivo
$go = "`nGO`n"

$script | % { $_ + $go} | Out-File "C:\Temp\DemoAspNet.sql"

Gerando scripts do banco de dados por .NET – Passo 2

Publicado em 01/02/2012 por Paulo R. Pereira

Após entendermos como utilizar o método de Script dos objetos do SMO com a finalidade de gerar scripts dos objetos do SQL Server, esta semana veremos como utilizar o Script, um objeto um pouco mais complexo com a finalidade de gerar o script de vários objetos de uma só vez.

Antes de conhecermos o objeto Scripter, é importante entender que os objetos do SQL Server no SMO possuem uma URN, um caminho para identifica-los no SQL Server, exemplo:

{Server[@Name=’SERVIDOR\INSTANCIA’]/
Database[@Name=’BANCO DE DADOS’]/
TipoDoObjeto[@Name=’OBJETO’ …]}

Com o nosso objeto Scripter, poderemos gerar scripts tanto informando os objetos que serão “scriptados” ou as URNs destes. Visto que as URN são uma forma mais simples de referenciar os objetos a serem “scriptados”, trabalharemos o nosso exemplo de utilização do Scripter com URNs:

using System;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    class DemoScripter
    {
        static void Main()
        {
            const string serverName = @".\SQLEXPRESS";

            const string databaseName = "DemoAspNet";

            const string scriptPath = @"C:\TEMP\DemoAspNet.sql";

            var server = new Server(serverName);

            var database = server.Databases[databaseName];

            var urn = new UrnCollection();

            Console.WriteLine("Carregando roles...");

            var roles = database.Roles.Cast<DatabaseRole>()
                .Where(obj => !obj.IsFixedRole)
                .Select(obj => obj.Urn);

            urn.AddRange(roles);

            Console.WriteLine("Carregando schemas...");

            var schemas = database.Schemas.Cast<Schema>()
                .Where(obj => !obj.IsSystemObject)
                .Select(obj => obj.Urn);

            urn.AddRange(schemas);

            Console.WriteLine("Carregando tables...");

            var tables = database.Tables.Cast<Table>()
                .Where(obj => !obj.IsSystemObject)
                .Select(obj => obj.Urn);

            urn.AddRange(tables);

            Console.WriteLine("Carregando views...");

            var views = database.Views.Cast<View>()
                .Where(obj => !obj.IsSystemObject)
                .Select(obj => obj.Urn);

            urn.AddRange(views);

            Console.WriteLine("Carregando procedures...");

            var procs = database.StoredProcedures.Cast<StoredProcedure>()
                .Where(obj => !obj.IsSystemObject)
                .Select(obj => obj.Urn);

            urn.AddRange(procs);

            using (var file = new StreamWriter(scriptPath))
            {
                Console.WriteLine("Script do banco de dados...");

                foreach (var str in database.Script())
                {
                    file.WriteLine(str);
                    file.WriteLine("GO");
                }

                file.WriteLine("USE [{0}]", database.Name);
                file.WriteLine("GO");

                Console.WriteLine("Script dos objetos...");

                //Definindo o Scripter e o ScriptOptions
                var scripter = new Scripter(server) { Options = { DriAll = true } };

                foreach (var str in scripter.Script(urn))
                {
                    file.WriteLine(str);
                    file.WriteLine("GO");
                }
            }

            Console.WriteLine("Script concluído ...");

            Console.ReadKey();
        }
    }
}

Neste exemplo, gerei o script das roles, schemas, tabelas, views e procedures de um banco de dados simples que possuo, mas de acordo com seu cenário, também será possível/necessário relacionar functions, índices e triggers da mesma forma que foi realizado com os outros objetos.

Gerando scripts do banco de dados por PowerShell – Passo 1

Publicado em 18/01/2012 por Paulo R. Pereira

Tento já explicado como utilizar o SMO para gerar o scripts do SQL Server por .NET, agora teremos a alternativa com PowerShell:

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll")

# Objetos
$srv = new-object Microsoft.SqlServer.Management.Smo.Server(".\SQLEXPRESS")

$db = $srv.Databases["DemoAspNet"]

# 1. Gerando o Script do banco de dados
$db.Script() | Out-File "C:\Temp\DemoAspNet.sql"

# 2. Gerando o Script das tabelas do banco de dados
$db.Tables | % { $_.Script() } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 3. Script com o GO entre as linhas (Forma 1)
$go = "`nGO`n"
$db.Tables | % { $_.Script() | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 4. Script com o GO entre as linhas (Forma 2)
$go = "
GO
"
$db.Tables | % { $_.Script() | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 5. Gerando o DROP das tabelas
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.ScriptDrops = $TRUE

$go = "`nGO`n"
$db.Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableDrop.sql"

# 6. Gerando o script das tabelas com as Primary Keys
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriPrimaryKey = $TRUE

$go = "`nGO`n"
$db.Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableKey.sql"

# 7. Gerando o script das tabelas com as Keys e Constraints, sem Collation
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriAll = $TRUE
$options.NoCollation = $TRUE

$go = "`nGO`n"
$db.Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableAll.sql"

Forma resumida para este script se executado dentro do próprio contexto do SQL Server (Jobs, PowerShell do SQL Server ou sqlps.exe):

# Sem referências

# Objetos
sl SQLSERVER:\SQL\LOCALHOST\SQLEXPRESS\Databases\DemoAspNet

$db = get-item .

# 1. Gerando o Script do banco de dados
$db.Script() | Out-File "C:\Temp\DemoAspNet.sql"

# 2. Gerando o Script das tabelas do banco de dados
dir Tables | % { $_.Script() } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 3. Script com o GO entre as linhas (Forma 1)
$go = "`nGO`n"
dir Tables | % { $_.Script() | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 4. Script com o GO entre as linhas (Forma 2)
$go = "
GO
"
$db.Tables | % { $_.Script() | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_Tables.sql"

# 5. Gerando o DROP das tabelas
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.ScriptDrops = $TRUE

$go = "`nGO`n"
dir Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableDrop.sql"

# 6. Gerando o script das tabelas com as Primary Keys
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriPrimaryKey = $TRUE

$go = "`nGO`n"
dir Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableKey.sql"

# 7. Gerando o script das tabelas com as Keys e Constraints, sem Collation
$options = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriAll = $TRUE
$options.NoCollation = $TRUE

$go = "`nGO`n"
dir Tables | % { $_.Script($options) | % { $_ + $go} } | Out-File "C:\Temp\DemoAspNet_TableAll.sql"

Artigo relacionado:

PowerShell no SQL Server – Step 1

Gerando scripts do banco de dados por .NET – Passo 1

Publicado em 17/01/2012 por Paulo R. Pereira

Criar scripts do banco de dados, ou de qualquer objeto do SQL Server por .NET não é algo muito complexo, pois os principais objetos do SMO possuem o método Script. Mas como gerar o script desejado é o que pode dar um pouco de dor de cabeça para quem esta começando com SMO, seja no .NET ou no PowerShell.

Para primeira demonstração, vamos ver como é simples gerar o script de criação do banco de dados:

using System;
using System.IO;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    class DemoScript1
    {
        static void Main()
        {
            const string serverName = @".\SQLEXPRESS";

            const string databaseName = "DemoAspNet";

            const string scriptPath = @"C:\TEMP\DemoAspNet.sql";

            var server = new Server(serverName);

            var database = server.Databases[databaseName];

            // Gerando o script do banco de dados e salvando este em um arquivo
            using (var file = new StreamWriter(scriptPath))
            {
                Console.WriteLine("Script do banco de dados...");

                foreach (var str in database.Script())
                {
                    file.WriteLine(str);
                    file.WriteLine("GO");
                }
            }

            Console.WriteLine("Script concluído ...");

            Console.ReadKey();
        }
    }
}

Agora a geração do script das tabelas do banco de dados:

using System;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    class DemoScript2
    {
        static void Main()
        {
            const string serverName = @".\SQLEXPRESS";

            const string databaseName = "DemoAspNet";

            const string scriptPath = @"C:\TEMP\DemoAspNet.sql";

            var server = new Server(serverName);

            var database = server.Databases[databaseName];

            using (var file = new StreamWriter(scriptPath))
            {
                Console.WriteLine("Script das tabelas...");

                // Relacionando as tabelas do banco de dados
                foreach (var table in database.Tables.Cast<Table>().Where(table => !table.IsSystemObject))
                {
                    // Gerando o script de cada uma das tabelas
                    file.WriteLine("--");
                    file.WriteLine("-- Tabela: [{0}].[{1}]", table.Schema, table.Name);
                    file.WriteLine("--");
                    foreach (var str in table.Script())
                    {
                        file.WriteLine(str);
                        file.WriteLine("GO");
                    }
                }
            }

            Console.WriteLine("Script concluído ...");
            Console.ReadKey();
        }
    }
}

Na geração do script das tabelas, é bem provável que você vá se perguntar “onde estão as constraints das tabelas?”, ai entra o objeto ScriptOptions, o qual pode ser utilizado como argumento para o método Script, afim de definir como e do que será gerado o script.

Para entender isso, temos abaixo um ScriptOptions configurado para gerar o script das tabelas com as Primary Keys (DriPrimaryKey):

var scriptOptions = new ScriptingOptions { DriPrimaryKey = true };

O ScriptOptions para gerar o script de DROP das tabelas (ScriptDrops):

var scriptOptions = new ScriptingOptions { ScriptDrops = true };

E para um exemplo final, utilizando o ScriptOption afim de gerar o script das tabelas com as Keys e Constraints (DriAll) e sem informar Collation das colunas (NoCollation):

using System;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    class DemoScript3
    {
        static void Main()
        {
            const string serverName = @".\SQLEXPRESS";

            const string databaseName = "DemoAspNet";

            const string scriptPath = @"C:\TEMP\DemoAspNet.sql";

            var server = new Server(serverName);

            var database = server.Databases[databaseName];

            //Definindo o objeto ScriptingOptions
            var scriptOptions = new ScriptingOptions { DriAll = true, NoCollation = true };

            using (var file = new StreamWriter(scriptPath))
            {
                Console.WriteLine("Script das tabelas...");

                foreach (var table in database.Tables.Cast<Table>().Where(table => !table.IsSystemObject))
                {
                    //ScriptingOptions com argumento do método Script
                    file.WriteLine("\n\n--\n-- Tabela: [{0}].[{1}]\n--\n", table.Schema, table.Name);
                    foreach (var str in table.Script(scriptOptions))
                    {
                        file.WriteLine(str);
                        file.WriteLine("GO");
                    }
                }
            }

            Console.WriteLine("Script concluído ...");
            Console.ReadKey();
        }
    }
}

No artigo da próxima semana veremos como utilizar o objeto Scripter para gerar o script de vários objetos ao mesmo tempo, e ainda nesta semana a versão deste artigo para PowerShell.

Script de Cidades e Estados [Update]

Publicado em 02/04/2011 por Paulo R. Pereira

Postei uma vez este link no fórum do MSDN e teve um bom resultado, trata-se de um script SQL para popular tabelas de Cidade, Estado e Pais no SQL Server, para mudar a estrutura do script nada melhor que o Regular Expression do Replace do SQL Server Management Studio (Já fiz isto muitas vezes, pois muitos projetos que participei tinham estrutas de tabelas de cidade/estado diferentes ou até mesmo banco de dados diferentes, como o MySQL, Interbase e Oracle). Recomendo também dar uma olhada nos arquivos, que como este, estão no meu Virtual Disc, pois sempre estou colocando arquivos úteis lá, como os materiais do Student To Business (FY 2009).

Download do Script

[2011-03-29] Problema com a limitação do 4shared fez com que meus arquivos compartilhados fossem enviados para o limbo (…)

[2011-04-01] Estou disponibilizando pelo mediafire um novo script SQL de Cidades e Estados:

http://www.mediafire.com/?2z0xfdhqmh2ysm3

[2011-04-02] Atendendo aos pedidos, também estou disponibilizando um arquivo CSV com a relação UF/Cidades:

http://www.mediafire.com/?mzx1467rlkyafkn

Iniciando com PowerShell – Step 2

Publicado em 16/08/2010 por Paulo R. Pereira

Visto que uma das idéias do PowerShell é a facilidade de criar scripts e executá-los posteriormente, para demonstrar este recurso utilizarei do Windows PowerShell ISE, que é uma ferramenta muito útil para editar e executar scripts.

Esta ferramenta possui uma interface bem descomplicada:

Podemos executar comandos diretamente na área prompt ou executar scripts que estamos editando apertando o botão assinalado, e acompanhar a execução na área resultados.

Quando você já tiver desenvolvido seu script e desejar executá-lo novamente, você pode abrir o PowerShell e informar onde o script esta localizado no seu computador, mas isso pode apresentar problemas por políticas de execução, exemplo:

Para isso execute o comando abaixo e ‘Y’ (Yes), pra eliminar a restrição a scripts:

Set-ExecutionPolicy RemoteSigned

E por fim, execute novamente seu script:

Queries para dicionário de dados – Step 2

Publicado em 19/07/2009 por Paulo R. Pereira

Continuando o post de Dicionário de dados,

Desde versões antigas do SQL Server são encontradas system view que permitem de várias formas mapear as entidades do banco de dados, desde relacionar as colunas de uma determinada tabela até mapear relacionamentos, índices e arquivos de armazenamento do banco de dados.

Primeiramente, como encontrar os relacionamentos no SQL Server 2000 e superiores:

SELECT
  OBJECT_NAME(foreigns.rkeyid) Parent_Table,
  OBJECT_NAME(foreigns.fkeyid) Child_Table,
  OBJECT_NAME(foreigns.constid) Key_Name,
  parent_columns.name Parent_Col,
  child_columns.name Child_Col
FROM sys.sysforeignkeys foreigns
INNER JOIN sys.syscolumns parent_columns
  ON foreigns.fkeyid = parent_columns.id
  AND foreigns.fkey = parent_columns.colid
INNER JOIN sys.syscolumns child_columns
  ON foreigns.rkeyid = child_columns.id
  AND foreigns.rkey = child_columns.colid
ORDER BY Parent_Table, Child_Table

A mesma funcionalidade no SQL Server 2005/2008:

SELECT
  OBJECT_NAME(foreigns_columns.[referenced_object_id]) Parent_Table,
  OBJECT_NAME(foreigns_columns.[parent_object_id]) Child_Table,
  OBJECT_NAME(foreigns_columns.[constraint_object_id]) Key_Name,
  parent_columns.name Parent_Col,
  child_columns.name Child_KeyCol
FROM sys.foreign_key_columns foreigns_columns
INNER JOIN sys.columns parent_columns
  ON parent_columns.[object_id] = foreigns_columns.[parent_object_id]
  AND parent_columns.[column_id] = foreigns_columns.[parent_column_id]
INNER JOIN sys.columns child_columns
  ON child_columns.[object_id] = foreigns_columns.[referenced_object_id]
  AND child_columns.[column_id] = foreigns_columns.[referenced_column_id]
ORDER BY Parent_Table, Child_Table

A mesma funcionalidade sem o uso da função “OBJECT_NAME”:

SELECT
  parent.name Parent_Table,
  child.name Child_Table,
  foreigns.name FKey_Name,
  parent_columns.name Parent_Col,
  child_columns.name Child_Col
FROM sys.tables parent
INNER JOIN sys.foreign_keys foreigns
  ON parent.[object_id] = foreigns.[referenced_object_id]
INNER JOIN sys.tables child
  ON child.[object_id] = foreigns.[parent_object_id]
INNER JOIN sys.foreign_key_columns foreigns_columns
  ON foreigns_columns.[constraint_object_id] = foreigns.[object_id]
INNER JOIN sys.columns parent_columns
  ON parent_columns.[object_id] = foreigns_columns.[parent_object_id]
  AND parent_columns.[column_id] = foreigns_columns.[parent_column_id]
INNER JOIN sys.columns child_columns
  ON child_columns.[object_id] = foreigns_columns.[referenced_object_id]
  AND child_columns.[column_id] = foreigns_columns.[referenced_column_id]
ORDER BY Parent_Table, Child_Table

Agora, para qualquer banco de dados ANSI (MySQL, SQL Server, SQL Lite, PostgreSQL, Oracle), utilizando as system views do INFORMATION_SCHEMA:

SELECT
  KU.TABLE_NAME Parent_Table,
  KU2.TABLE_NAME Child_Table,
  FK.CONSTRAINT_NAME FKey_Name,
  KU.COLUMN_NAME Parent_Col,
  KU2.COLUMN_NAME Child_Col
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KU
  ON KU.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KU2
  ON KU2.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
  AND KU.ORDINAL_POSITION = KU2.ORDINAL_POSITION

Queries para dicionário de dados – Step 1

Publicado em 11/07/2009 por Paulo R. Pereira

Olá pessoal,

Uma coisa complicada quando se modela ou dá suporte a um bancos de dados, é entender a sua estrutura, para isso pensa-se em documentá-los com o Diagrama de Modelo de Entidades e Relacionamentos (DER ou MER) e/ou com o Dicionário de Dados (DD).

Na categoria Visio do Blog (at. https://sqlfromhell.wordpress.com/category/visio/ ) existe uma série de post descrevendo como fazer o Diagrama de Modelo de Entidades e Relacionamentos (DER ou MER), e no próprio Visio também pode ser feito o Dicionário de Dados (DD).

Como nem todos utilizam ou têm acesso ao Visio, neste post vou relacionar as queries utilizadas para consultar a relação de Tabelas, Colunas e Tipos de dados existentes no SQL Server 2000 ao 2008, que são úteis para realizar outras atividades, como “geradores de código” e “relatórios dinâmicos”.

Primeiramente, para relacionar as tabelas do SQL Server podemos fazer uso de system views para SQL Server 2005 e superiores:

SELECT * FROM sys.tables

ou para SQL Server 2000 e superiores:

SELECT * FROM sysobjects
WHERE xtype LIKE 'U'

Para relacionar as tabelas e suas respectivas colunas para SQL Server 2005 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna
FROM sys.tables T
INNER JOIN sys.columns C
  ON C.object_id = T.object_id

ou para SQL Server 2000 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna
FROM sysobjects T
INNER JOIN syscolumns C
  ON T.id = C.id
  AND T.xtype LIKE 'U' -- Verifica se o tipo de objeto é uma tabela

Agora uma query from hell para relacionar as colunas ao seus respectivos tipos para SQL Server 2005 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna,
  TY.name as Tipo,
  C.max_length, -- Tamanho em bytes, para nvarchar normalmente se divide este valor por 2
  C.precision, -- Para tipos numeric e decimal (tamanho)
  C.scale -- Para tipos numeric e decimal (números após a virgula)
FROM sys.columns C
INNER JOIN sys.tables T
  ON T.object_id = C.object_id
INNER JOIN sys.types TY
  ON TY.user_type_id = C.user_type_id
ORDER BY T.name, C.name

ou para SQL Server 2000 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna,
  TY.name as Tipo,
  C.length, -- Tamanho em bytes, para nvarchar normalmente se divide este valor por 2 ou utiliza a 'prec'
  C.prec, -- Para tipos numeric e decimal (tamanho)
  C.scale -- Para tipos numeric e decimal (números após a virgula)
FROM syscolumns C  
INNER JOIN sysobjects T
  ON T.id = C.id
  AND T.xtype LIKE 'U' -- Verifica se o tipo de objeto é uma tabela
INNER JOIN systypes TY
  ON C.usertype = TY.usertype
  AND C.xtype = TY.xtype
ORDER BY T.name, C.name

Agora, para qualquer banco de dados ANSI (MySQL, SQL Server, SQL Lite, PostgreSQL, Oracle), utilizando as system views do INFORMATION_SCHEMA, relacionando as colunas e seus respectivos tipos e tabelas:

SELECT
 TABLE_NAME as Tabela,
 COLUMN_NAME as Coluna,
 DATA_TYPE as Tipo,
 CHARACTER_MAXIMUM_LENGTH, 
 NUMERIC_PRECISION, 
 NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY Tabela, Coluna

ou somente as tabelas:

SELECT
 TABLE_NAME as Tabela
FROM INFORMATION_SCHEMA.TABLES
ORDER BY Tabela

Pessoal até o próximo post!

Ferramenta on-line para formatar o SQL

Publicado em 08/06/2009 por Paulo R. Pereira

Estive procurando na internet algo para organizar queries SQL, encontrei alguns plugins para o SQL Server Management Studio e para o Visual Studio e outras ferramentas fraquinhas para esta funcionalidade, mas nenhuma acessível o bastante para minha mudança frequentes de equipamentos, algo do tipo, “qualquer lugar que tenha internet, eu tenha acesso a ela, sem ter que instalar nada”. Até encontrar este site (at. http://www.dpriver.com/pp/sqlformat.htm ), que alem de deixar o código legível, tem de adaptações para “ctrl+c ctrl+v” quando se pensar em colocar a query dentro de um código C#, Java ou PHP, compatibilidade com MySQL, Oracle, SQL Server entre outras customizações de layout.

Piores práticas: NOT IN

Publicado em 25/05/2009 por Paulo R. Pereira

Pessoal, para não chover no molhado falando sobre minha recusa em utilizar o operador NOT IN, segue abaixo um dos melhores artigos sobre o assunto, este do Gustavo Maia Aguiar.

http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!280.entry

SQL From Hell.com

Um nome bem intuitivo para muita informação!

Arquivo da tag: Script

Gerando scripts do banco de dados por PowerShell – Passo 2

Gerando scripts do banco de dados por .NET – Passo 2

Gerando scripts do banco de dados por PowerShell – Passo 1

Gerando scripts do banco de dados por .NET – Passo 1

Script de Cidades e Estados [Update]

Download do Script

Iniciando com PowerShell – Step 2

Queries para dicionário de dados – Step 2

Queries para dicionário de dados – Step 1

Ferramenta on-line para formatar o SQL

Piores práticas: NOT IN

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Download do Script

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso: