sysobjects, sys.objects, syscolumns e syscomments

Utilize as tabelas de sistema do SQL Server para turbinar seu trabalho

As tabelas de sistema contém informações sobre a estrutura do seu banco de dados. Estas estruturas permitem a criação de dinâmicas de código que facilitam o dia-a-dia do programador SQL. Todas estão disponíveis a partir do SQL 2000 (com excessão da tabela SYS.OBJECTS, disponível a partir da versão 2005). Vamos conhecer as tabelas.

SYSOBJECTS
Esta tabela armazena todos os objetos do banco de dados, tais como tables, procedures, triggers, views, functions, foreign keys, constraints, entre outros. Resumindo, todos os objetos de usuário estão nesta tabela. As principais colunas da tabela são:

• Name: nome do objeto no banco de dados
• crdate: data de criação do objeto
• type: tipo do objeto.

Alguns exemplos de aplicação:

Code Snippet

1. --
2.  
3. select name, crdate, type
4.   from sysobjects
5. where name like 'spcFAE%'
6. order by crdate desc
8. --

Retorna o nome, data de criação e tipo dos objetos que o nome comece com “spcFAE”, ordenando por data de criação descescente.

Code Snippet

1. --
2.  
3. select name, crdate, type
4.   from sysobjects
5. where crdate > '2010-05-01'
6. order by crdate desc
7.   
8. --

Retorna todos os objetos criados no banco de dados a partir de 01 de maio de 2010.

Code Snippet

1. --
2.  
3. select name
4.   from sysobjects
5. where type in ('P','TR','FN')
6. order by crdate desc
7.   
8. --

Retorna o nome dos procedimentos (P), triggers (TR) e functions (FN).

Estas pesquisas na tabela SYSOBJECTS são muito úteis, por exemplo, para buscar todos os procedimentos criados a partir de uma determinada data, ou relacionar rotinas por nome, etc. Existe ainda (somente a partir da versão 2005 do SQL), a tabela SYS.OBJECTS, que funciona como uma extensão da tabela SYSOBJECTS (relaciona pela coluna ID), porém possui uma coluna que é muito útil para desenvolvedores, a coluna MODIFY_DATE. Esta coluna armazena a data da última alteração de um objeto.

Imagine a seguinte situação, você trabalha 5 dias corrigindo em uma base teste dezenas de procedimentos. Você não precisa mais ficar anotando qual rotina alterou para criar o script no final, basta buscar da tabela sys.objects os objetos onde o modify_date seja maior ou igual ao dia que começou a trabalhar… bacana não é?

Os tipos conhecidos mais comuns de objetos na tabela (coluna type) são:

• U  - TABLE (tabela)
• F  - FOREIGN KEY (chaves estrangeiras)
• C  - CHECK CONSTRAINT
• D  - DEFAULT
• P  - STORED PROCEDURE
• V  - VIEW
• TR – TRIGGER
• TF - TABLE FUNCTION
• FN - USER DEFINED FUNCTION
• S  - SYSTEM TABLE
• K  - PRIMARY KEY (chave primária)

A tabela sysobjects também pode ser utilizada em conjunto com outras tabelas de sistema, para outros tipos de pesquisa no banco de dados. Vamos conhecer agora a tabela SYSCOLUMNS.

SYSCOLUMNS
Esta tabela armazena armazena as colunas de tabelas, parametros de entrada de procedimentos, e assim por diante. Toda vez que é criada uma nova tabela ou procedimento no sistema, o SQL grava nesta tabela informações preciosas, que podemos utilizar a qualquer momento. As colunas que mais vamos utilizar neste exemplo são:

ID	é a identificação do objeto a quem pertence estas colunas [ syscolumns.id = sysobjects.id ]
xtype	determina o tipo de coluna (inteiro, decimal, char, varchar, bit, etc). O tipo é um código, encontrado em systypes [ syscolumns.xtype = systypes.xtype ]
length	tamanho do campo (no caso char, varchar terá o tamanho específico).
xprec & xscale	especifico para tipos decimais, informando a quantidade total de dígitos com decimais (xprec), e a quantidade de decimais (xscale)
allownulls	valor bit (0/1) que informa se a coluna aceita valores nulos ou não.
collation	Especifica o COLLATION da coluna (para tipos texto). Isto é necessário para solucionar problemas de conflitos de bancos de dados de diferentes idiomas.

A partir desta tabela, é possível ler qualquer estrutura do banco de dados. Qual a utilidade disso? Como uma pequena demonstração do que podemos fazer com a tabela SYSCOLUMNS, vamos criar um procedimento de apoio (?). Bom, um procedimento de apoio é um código feito para gerar código. É isto mesmo!

Vamos exemplificar.

Temos uma tabela chamada NotaItemLote (tabela onde são gravados os números de lote dos itens de uma nota fiscal). Você está criando uma rotina que irá inserir dados nesta tabela (gerando uma nota fiscal). Conhecendo a dinâmica de bancos de dados, quando construo uma query de insert, eu sempre especifico o nome dos campos da tabela que estou inserindo dados:

 

Code Snippet

1. --
2.  
3. insert into tabela (coluna1, coluna2, coluna3) select 1, 2, 3
4.   
5. --

Isto garante que no caso de uma eventual atualização no sistema, se forem adicionadas colunas novas nesta tabela que aceitem nulo, minha rotina ficará estável e não dará erros. Se você não especifica o nome das colunas no insert, ao alterar a estrutura da tabela, todas as rotinas que geram insert sem especificar o nome darão erro. Isto é padronização básica da básica.

Pois bem, como vamos gerar um insert então? Muito simples! Altere o nome da tabela para uma que exista no seu banco de dados, e veja o resultado do select abaixo (utilizando syscolumns)

Code Snippet

1. --
2.  
3. select syscolumns.name + ', '
4.   from syscolumns, sysobjects
5. where syscolumns.id = sysobjects.id
6.    and sysobjects.name = 'NotaItemLote'
7. order by syscolumns.colorder
8.   
9. --

A query irá retornar o nome das colunas da tabela, em sequência, e ainda colocando uma virgula ao nome do lado, para facilitar. Aí basta copiar, colar e montar a sua query de insert

Code Snippet

1. --
2.  
3. insert into NotaItemLote (cd_empresa, nr_serie, nr_nota, cd_item
4.                         , nr_lote, dt_validade, qt_lote, sinal) ...
5.   
6. --

Então vamos melhorar mais ainda, e facilitar a utilização deste procedimento, pois não teria sentido digitar uma query como essa toda vez que quisesse pegar as colunas de uma tabela. Então vamos criar um procedimento chamado sp_gerainsert. Segue o código dele abaixo:

Code Snippet

1. --
2.  
3. create proc sp_gerainsert
4.   @objname as sysname
5. as
6.  
7.   select name + ', '
8.     from syscolumns
9.    where id = object_id(@objname)
10.    order by colorder
11.      
12. --

*A função object_id retorna o ID do objeto passado como parâmetro (o mesmo ID da tabela sysobjects).

Após compilar o procedimento, vá até o menu TOOLS (ferramentas), OPTIONS (opções). Selecione KEYBOARD, e insira a função que criamos no atalho CTRL+5 (pode vincular em qualquer atalho que desejar):


  
Agora, toda vez que for criar uma query de insert em uma tabela, basta selecionar o nome da tabela na janela de query, e pressionar CTRL+5 (ou o atalho que tenha especificado):

SYSCOMMENTS
A tabela syscomments é onde ficam armazenados todos os textos de procedimentos, triggers, funções, etc. Quando você executa um CREATE PROCEDURE, por exemplo, é nesta tabela que fica armazenado todo o código da procedure que compilou.

Vamos então para a parte prática. Digamos que você esteja fazendo uma atualização no seu banco de dados. Uma tabela de uso frequente recebeu uma nova coluna, e esta coluna não aceita valores nulos. Isto significa que você precisa revisar todos os procedimentos que inserem dados nesta tabela, correto? Para entender a forma como vamos procurar isso na tabela syscomments, vamos primeiro analisar uma query de insert:

Code Snippet

1. --
2.  
3. insert into NotaItemLote (cd_empresa, nr_serie, nr_nota, nr_lote
4.                         , dt_validade, qt_lote, sinal)
5.   select 1, 'NFF', 1525, 'A0001', '2010-12-31', 10.0 , 'E'
6.      
7. --

 

Muito bem, o que precisamos fazer então, é localizar todos os procedimentos, triggers ou funções que contenham o seguinte texto: “insert into NotaItemLote”. Vamos então montar a query de busca na syscomments da seguinte forma:

Code Snippet

1. --
2.  
3. select name
4.   from sysobjects
5. where id in (select name
6.                 from syscomments
7.                where text like '%into notaitemlote%')
8.                     
9. --

O resultado da query é o nome de todos os procedimentos que contenham este texto “into notaitemlote”. Quem está acostumado com atualizações em bancos de dados que contém procedimentos, conhece bem esta rotina.

 

Estas dicas, aplicadas no dia-a-dia, garantem muito mais produtividade ao programador, pois não precisa ficar digitando nome de colunas, localiza os objetos alterados no sistema, etc. É muito mais controle sobre os objetos para o programador. E pode ter certeza, utilizar estes indicadores (como data de criação, data de alteração da rotina, etc) garantem muito mais precisão do que documentação manual.