La struttura di una tabella

pubblicato il 27/10/2008

La struttura di una tabella può essere recuperata con una (quasi) semplice istruzione Select: ovviamente il segreto è puntare alle tabelle giuste! E le cose posso cambiare a seconda dell'utente connesso al database.

Utente sa

Le informazioni sulle tabelle sono contenute in alcune tabelle di sistema, quelle il cui nome comincia con sys*: queste tabelle però sono accessibili soltanto utilizzando l'utente 'sa'.

Ad esempio, la tabella sysobjects riporta un record per ogni oggetto (tabella, vista, stored procedure, trigger o funzione) definito nel database; il campo xtype definisce il tipo di oggetto, perciò per limitare la ricerca alle sole tabelle utente basterà inserire una clausola Where xtype = 'u':

SELECT * FROM SysObjects WHERE xtype = 'u'

L'elenco completo dei tipi possibili è facilmente reperibile nei Books On Line, cercando proprio la parola sysobjects.

Volendo cercare informazioni riguardo ad una tabella specifica, basterà scrivere nella clausola Where i lnome appropriato:

SELECT * FROM SysObjects WHERE [Name] = 'Clienti' AND xtype = 'u'

Allo stesso modo, la tabella syscolumns riporta le informazioni relative alle colonne che compongono le tabelle e le viste nel database; il legame tra syscolumns e sysobjects è instaurato attraverso il campo id nelle due tabelle.

Per quanto riguarda invece la definizione dei tipi di dato si deve fare riferimento alla tabella systypes: fin qui sembre tutto facile. Le cose però si complicano, ma solo leggermente, se consideriamo che nella definizione di una colonna possiamo usare uno dei tipi di dati predefiniti di Sql Server (quali int o datetime, ad esempio) oppure usare un tipo di dato definito dall'utente: ad esempio, per avere coerenza nel sistema potremmo definire un tipo di dato udtCodice a sua volta di tipo char(6).

In considerazione di ciò, per determinare il tipo utente che definisce una colonna la tabella systypes deve essere legata alla syscolumns tramite i rispettivi campi xusertype; per determinare invece il sottostante tipo nativo Sql occorre mettere in join la tabella systypes con sé stessa tramite i due campi xusertype e xtype.

Infine, la descrizione associata ad ogni colonna è reperibile nella tabella sysproperties che contiene i valori di tutte le proprietà definibile in un database Sql Server: in particolare, le descrizione delle colonne si trovano memorizzate con il nome 'MS_Description'.

Mettendo insieme i vari pezzi, otteniamo lo script:

DECLARE @NomeTabella AS VARCHAR(50)
SET @NomeTabella = 'Clienti'

SELECT syscolumns.colorder AS 'Pos.',
       syscolumns.name AS Colonna,
       systypes.name AS UDDT,
       B.Name AS Type,
       syscolumns.prec as 'Dim.',
       CASE syscolumns.isnullable WHEN 1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable,
       CAST(ISNULL(sysproperties.[value], '') AS varchar(1000)) AS Descrizione
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN systypes ON syscolumns.xusertype = systypes.xusertype
INNER JOIN systypes B ON B.xusertype = systypes.xtype
LEFT OUTER JOIN sysproperties ON sysproperties.id = syscolumns.id
                     AND sysproperties.smallid = syscolumns.colid
                     AND sysproperties.name = 'MS_Description'
WHERE (sysobjects.xtype = 'U' )
  AND (sysobjects.name = @NomeTabella )
ORDER BY syscolumns.colorder

Utente non sa

Lo script precedente è utilizzabile soltanto accedendo al database in questione con l'utente 'sa'; come fare per determinare la struttura di una tabella se abbiamo a disposizione un utente con privilegi limitati? Ci vengono incontro le viste INFORMATION_SCHEMA.

Le viste INFORMATION_SCHEMA sono delle viste, residenti nel database master ed appartenenti all'utente INFORMATION_SCHEMA - appunto. Esse rimappano i valore delle tabelle di sistema e sono accessibili a tutti, in sola lettura. La vista che ci interessa si chiama COLUMNS la sua particolarità è che deve essere richiamata (analogamente alle altre viste della stessa famiglia) anteponendo il nome del proprietario, quindi INFORMATION_SCHEMA.COLUMNS.

Per riscrivere lo script precedente è necessario integrare le viste usando la funzione di sistema fn_listextendedproperty che restituisce la descrizione associata all'oggetto indicato dai parametri che le vengono passati (per l'elenco dei parametri si possono consultare i Books On Line). La funzione restituisce una tabella che può essere messa in join con la vista COLUMNS tramite il suo campo OBJNAME che deve corrispondere al campo COLUMN_NAME della vista.

A questo punto lo script può essere riscritto in questo modo:

DECLARE @NomeTabella AS VARCHAR(50)
SET @NomeTabella = 'CA_Profili'

SELECT ORDINAL_POSITION AS Pos,
       CAST(INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AS VARCHAR(40)) AS 'Colonna',
       INFORMATION_SCHEMA.COLUMNS.DOMAIN_NAME AS 'UDDT',
       CAST(DATA_TYPE AS VARCHAR(15)) AS 'Tipo',
       CHARACTER_MAXIMUM_LENGTH AS 'Dim.',
       IS_NULLABLE AS 'Nullable',
       CAST(ISNULL(COLUMN_DEFAULT, '') AS VARCHAR(12)) AS 'Default',
       CAST(value AS VARCHAR(250)) as Descrizione
FROM INFORMATION_SCHEMA.COLUMNS
LEFT JOIN ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @NomeTabella, 'column', NULL)
       ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = OBJNAME
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = 'dbo'
  AND INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG = DB_NAME()
  AND INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @NomeTabella
ORDER BY Pos