Usando como exemplo o banco PUBS e montando um simples SELECT nesta views, poderemos obter informações sobre a tabela authors conforme abaixo:
SELECT left(table_name,30)as tabela,left(column_name,25) as campo, is_nullable,left(data_type,10) as tipo,character_maximum_length as tamanho,
numeric_precision as precisão,numeric_scale as escala
FROM information_schema.columns
WHERE table_name=’authors’
RESULTADO:
tabela campo is_nullable tipo tamanho precisão escala
———- ——– ———– ———- ——- ——— ——–
authors au_id No varchar 11 NULL NULL
authors au_lname No varchar 40 NULL NULL
authors au_fname No varchar 20 NULL NULL
authors phone No char 12 NULL NULL
authors address YES varchar 40 NULL NULL
authors city YES varchar 20 NULL NULL
authors state YES char 2 NULL NULL
authors zip YES char 5 NULL NULL
authors contract No bit NULL 1 0
Ok, agora suponhamos que nossa necessidade é obter estas mesmas informações para todas as tabelas do banco Pubs, ou melhor, suponhamos que precisamos destas informações mas nunca sabemos para qual tabela ?
Eu lhe digo que se transformarmos este SELECT em uma procedure, poderemos facilmente atingir este objetivo. Veja a procedure abaixo:
USE Pubs
GO
CREATE PROC sp_getestrutura @tabela varchar (30)=null
AS
IF @tabela is null
— Executa para todas as tabelas do banco
SELECT left(table_name,30)as tabela,left(column_name,25) as campo,
is_nullable,left(data_type,10) as tipo,character_maximum_length as tamanho,
numeric_precision as precisão,numeric_scale as escala
FROM information_schema.columns
WHERE table_name NOT LIKE ‘sys%’ AND table_name NOT LIKE ‘dt_%’
else
— Executa apenas para a tabela informada
SELECT left(table_name,30)as tabela,left(column_name,25) as campo,
is_nullable,left(data_type,10) as tipo,character_maximum_length as tamanho,
numeric_precision as precisão,numeric_scale as escala
FROM information_schema.columns
WHERE table_name=@tabela
GO
Desta forma, se passamos o nome da tabela, serão obtidas informações apenas para a dada tabela. Se não passamos o nome, será obtido informações para todas as tabelas da base.
Exemplo: sp_getestrutura ou sp_getestrutura ‘authors’
Agora suponhamos que queremos ir ainda mais longe, como por exemplo, executar a procedure para qualquer database.
Da forma como foi criada ela funcionará apenas para a base Pubs, para executá-la na base NorthWind teríamos que criá-la também na base NorthWind e assim por diante.
Porém, se criarmos a procedure no database MASTER e depois transformá-la em um procedure de sistema, poderemos então executá-la a partir de qualquer base.
Para isto, recrie a procedure no master e execute o comando abaixo para convertê-la em uma procedure de sistema.
— Transforma a proc em uma proc de sistema
exec sp_MS_marksystemobject sp_getestrutura
go
Agora podemos executá-la a partir de qualquer base, simplesmente informando o nome da base antes da procedure como nos exemplos a seguir.
Exemplos:
pubs.dbo.sp_getestrutura ‘authors’ — Apenas para a tabela authors
pubs.dbo.sp_getestrutura — Para todas as tabelas
NorthWind..sp_getestrutura ‘customers’
NorthWind..sp_getestrutura