Obtendo informações sobre as tabelas de um banco SQL Server

Criar documentação para um sistema é sempre uma parte aborrecida e frequentemente negligenciada justamente por causa disso. No caso da ABC71, além da documentação das telas que é distribuída junto com o sistema, nós temos um documento que descreve as tabelas existentes no banco de dados. Sempre que uma tabela ou coluna nova é criada (ou modificada), nós registramos uma pequena descrição dela em uma tabela nossa, específica para esse fim.

Esse documento que descreve as tabelas é destinado a quem precise dar manutenção em dados diretamente no banco (parceiros, consultores, usuários avançados, etc.). Obviamente, ter apenas um breve descritivo de cada campo não é suficiente para esse objetivo - até porque é comum esquecer de alimentar a tal tabela.

Para gerar um documento mais completo, uso um conjunto de views do próprio SQL Server que permite obter informações sobre qualquer objeto existente no banco : tabelas e suas colunas, chaves, triggers, stored procedures, constraints, etc. Neste post, mostro como obter informações básicas, como a lista de tabelas, suas colunas e chaves.

A principal dessas views é a sys.sysobjects cujas linhas produzem um inventário dos objetos existentes no banco. As principais colunas retornadas por essa view estão descritas abaixo.

name é o nome de um objeto
id é o identificador único de cada objeto, independente do tipo desse objeto. O id é chave estrangeira em outras views, podendo ser usado para obter mais detalhes a respeito de um objeto. Por exemblo, há uma view que relaciona as colunas de uma determinada tabela e, nessa view, o id identifica qual é a tabela associada.
xtype identifica o tipo do objeto em cada linha da view. Essa coluna pode ser usada para filtrar os registros corretos quando for necessário um join para obter mais detalhes em outras views, como no caso das colunas citado acima. Os tipos possíveis podem ser encontrados neste link do MSDN.

Como exemplo, a query abaixo lista as tabelas de um banco.

select id, name from sys.sysobjects where xtype = 'U'

Efeito semelhante pode ser obtido simplesmente listando as linhas da view systables:

select * from sys.systables

As colunas de uma tabela são recuperadas acessando-se a view sys.syscolumns, cujas principais colunas eu comento abaixo.

name é o nome da coluna.
id identifica a tabela a qual essa coluna pertence. É o mesmo id encontrado em sys.sysobjetcs, podendo ser relacionado com ele para obter o nome da tabela.
Em type encontramos a identificação do tipo de dado atribuído à coluna. O nome para o tipo pode ser localizado na tabela sys.types. Para tipos núméricos, as colunas prec (precisão) e scale também deve ser levadas em consideração. No caso de tipos alfanuméricos ou binários, a coluna lenght determinará o tamanho máximo permitido.
O status informa, por exemplo, se essa coluna aceita o valor NULL. Para saber quais são os valores possíveis, veja a documentação no MSDN.
A ordem com que cada coluna foi criada dentro da tabela é estabelecida pelo campo colorder.

Se você não estiver trabalhando com tipos de dados criados pelo usuário, a query abaixo consegue listar as colunas de todas as tabelas de seu banco:

select *,
(select name from sys.types t
where c.type = t. system_type_id
and t.is_user_defined = 0) As Tipo
from sys.syscolumns c
--where c.id = 0 /* use o id de uma tabela p/ obter as colunas específicas */

Para levantar quais são as chaves criadas para uma tabela, podemos usar as informações retornadas pela view sys.sysindexes em conjunto com as linhas da view sys.sysindexkeys. Basicamente, a primeira view traz os nomes das chaves enquanto a segunda traz as colunas que constituem cada chave. Os campos mais relevantes da sys.sysindexes são os seguintes:

name é o nome atribuído à chave.
id novamente identifica uma tabela, cujo nome pode ser obtido relacionando-se esse campo com id encontrado em sys.sysobjetcs.
indid é um código único definido por tabela e que serve para identificar cada chave dessa tabela. Aparentemente, o valor 0 (zero) é sempre um valor interno do SQL; portanto, nos interessa apenas os registros cujo valor é maior que zero.
keycnt retorna a quantidade de campos que compõem a chave.
Use o status para determinar se a chave é primária, isso é, se os valores dos campos dessa chave poderão ser repetidos em outros registros ou não.

Na view sys.sysindexkeys encontramos os campos id (a tabela em sysobjects), indid (a chave em sysindexes) e colid (a coluna que compõe a chave, relacionada com a view syscolumns). O último campo é keyno, que indica a ordem em que as colunas aparecem na chave. A query abaixo usa essas informações para obter os nomes das colunas que compõem uma chave:

select I.name as Chave, IK.keyno as Ordem, c.name as Coluna
from sys.sysindexes I, sys.sysindexkeys IK, sys.syscolumns C
where I.id = IK.id
and I.id = c.id
and I.indid = IK.indid
and IK.colid = C.colid
and I.id = 2042867486 /* id da tabela */
and IK.indid = 2 /* número de uma chave da tabela */
order by IK.keyno

Note que há valores fixos para o objeto (tabela) e para a chave. Esse números devem ser substituídos por códigos válidos no seu banco de dados.

Mais Informações
Views do SQL Server para informações sobre um banco de dados