Retornando uma consulta SQL em XML
fevereiro 21, 2008
Depois de algum tempo sem postar, estou de volta. Estive um pouco ocupado e em busca de novos conhecimentos. Mas chega de ficar falando besteira e vamos ao que realmente interessa.
Estive pesquisando e encontrei uma maneira de gerar a saída de uma consulta no formato XML utilizando uma consulta em SQL.
SELECT
Clientes.ID,
Pedidos.Descricao,
Clientes.Nome
FROM
Clientes,
Pedidos
WHERE
Clientes.Nome LIKE ‘%Alexandre%’
ORDER BY
Pedidos.ID
FOR XML AUTO
A consulta acima exibirá como resultado (em formato XML) os clientes que contenham ‘Alexandre’ no nome ordenando pelo(s) pedido(s) existente(s).
A cláusula AUTO cria uma hierarquia dos elementos retornados automaticamente. Ex.: Existe o cliente ‘Alexandre’ e o mesmo possue dois pedidos, neste caso o retorno do XML será similar a este:
<Clientes ID=”1″ Nome=”Alexandre”>
<Pedidos Descricao=”Pedido Pendrive 4GB”/>
<Pedidos Descricao=”Pedido Televisor 29pol”/>
</Clientes>
Os nós de pedidos estão entre os clientes, criando assim uma hierarquia.
Provavelmente continuarei este artigo posteriormente, porque existe outras maneiras de utilizar esta técnica. Até a próxima.
Usando o CHARINDEX em SQL
dezembro 3, 2007
O CHARINDEX é uma função em SQL que retorna a primeira posição de uma sequencia de caracteres em uma determinada expressão. A primeira posição de contagem é 1.
Sintaxe:
CHARINDEX(expressao1, expressao2 [, posicao_inicial])
expressao1: É onde contém a sequencia de caracteres a ser encontrado.
expressao2: É onde contém a sequencia de caracteres que será feito a busca. Geralmente, é usado uma coluna de um campo.
posicao_inicial: É um parâmetro opcional, que deve-se colocar a posição inicial da busca, caso omitido, será iniciado do zero.
Segue abaixo a sintaxe:
SELECT
Nome,
CHARINDEX(‘papel’, Nome) AS Posicao
FROM
Produto
Na situação acima a busca será iniciada na primeira posição do campo ‘Nome’ da tabela ‘Produto’, porque o último parâmetro que indica a posição inicial de busca foi omitido.
Como inventer os caracteres em SQL
novembro 22, 2007
O SQL possui uma função que faz a inverção dos caracteres passados como parâmetro. O parâmetro informado pode ser constante, variáveis, colunas retornadas de um campo e até mesmo um números, seu resultado sempre será VARCHAR.
Sintaxe:
REVERSE(expressão)
SELECT REVERSE(‘Alexandre’) AS ‘Nome’
O resultado da consulta acima será ‘erdnaxelA’, preservando os caracteres maiúsculos e/ou minúsculos.
Quantidade de processamento em SQL
novembro 22, 2007
A palavra-chave @@CPU_BUSY retorna o tempo em milisegundos que a CPU está utilizando desde a última inicialização do SQL Server.
Veja abaixo um exemplo de como usar este recurso:
SELECT @@CPU_BUSY AS ‘CPU ms’, GETDATE() AS ‘Até’
Acima será mostrado a quantidade de processamento que a CPU utilizou até o momento da data.
Alterando / Removendo campos de uma tabela em SQL
novembro 10, 2007
O ALTER TABLE é usado para adicionar e/ou remover colunas de uma determinada tabela que esteja em um base de dados. Para isto é necessário informar o nome da tabela, o campo da tabela e caso seja uma adição, será necessário passar o tipo do novo campo.
ALTER TABLE
Cliente
ADD
CPF CHAR(11)
Acima foi criado o campo ‘CPF’ na tabela ‘Cliente’ com o tipo CHAR. Para que o campo aceite valores nulos (NULL), basta adicionar o operador ‘=’ e em seguida a palavra NULL.
Já para remover um campo de uma tabela é ainda mais simples, não havendo a necessidade de inserir o tipo do campo.
ALTER TABLE
Cliente
DROP COLUMN
CPF
Lembrando que, ao remover um campo de uma tabela, não haverá a possibilidade de voltar atrás (não pelo meu conhecimento), a não ser que faça um backup manual ou algo parecido.
Como saber as características de um objeto em SQL
novembro 7, 2007
A linguagem SQL possui uma função que exibe informações sobre todos os objetos existentes na base de dados.
Sintaxe:
OBJECTPROPERTY(id, propriedade)
Argumentos:
id -> É a identificação do objeto na base de dados, ou seja, uma identificação única e que pode ser encontrada na tabela sysobjects. Lembrando que este argumente DEVE ser do tipo inteiro.
propriedade -> É onde insere a expressão que se deseja obter sobre o objeto.
Segue abaixo todas as propriedades existentes no SQL Server:
Property name | Object type | Description and values returned |
CnstIsClustKey | Constraint | A primary key with a clustered index. 1 = True0 = False |
CnstIsColumn | Constraint | COLUMN constraint. 1 = True0 = False |
CnstIsDeleteCascade | Constraint | A foreign key constraint with the ON DELETE CASCADE option. |
CnstIsDisabled | Constraint | Disabled constraint. 1 = True0 = False |
CnstIsNonclustKey | Constraint | A primary key with a nonclustered index. 1 = True0 = False |
CnstIsNotTrusted | Constraint | Constraint was enabled without checking existing rows, so constraint may not hold for all rows. 1 = True0 = False |
CnstIsNotRepl | Constraint | The constraint is defined with the NOT FOR REPLICATION keywords. |
CnstIsUpdateCascade | Constraint | A foreign key constraint with the ON UPDATE CASCADE option. |
ExecIsAfterTrigger | Trigger | AFTER trigger. |
ExecIsAnsiNullsOn | Procedure, Trigger, View | The setting of ANSI_NULLS at creation time. 1 = True0 = False |
ExecIsDeleteTrigger | Trigger | DELETE trigger. 1 = True0 = False |
ExecIsFirstDeleteTrigger | Trigger | The first trigger fired when a DELETE is executed against the table. |
ExecIsFirstInsertTrigger | Trigger | The first trigger fired when an INSERT is executed against the table. |
ExecIsFirstUpdateTrigger | Trigger | The first trigger fired when an UPDATE is executed against the table. |
ExecIsInsertTrigger | Trigger | INSERT trigger. 1 = True0 = False |
ExecIsInsteadOfTrigger | Trigger | INSTEAD OF trigger. |
ExecIsLastDeleteTrigger | Trigger | The last trigger fired when a DELETE is executed against the table. |
ExecIsLastInsertTrigger | Trigger | The last trigger fired when an INSERT is executed against the table. |
ExecIsLastUpdateTrigger | Trigger | The last trigger fired when an UPDATE is executed against the table. |
ExecIsQuotedIdentOn | Procedure, Trigger, View | The setting of QUOTED_IDENTIFIER at creation time. 1 = True0 = False |
ExecIsStartup | Procedure | Startup procedure. 1 = True0 = False |
ExecIsTriggerDisabled | Trigger | Disabled trigger. 1 = True0 = False |
ExecIsUpdateTrigger | Trigger | UPDATE trigger. 1 = True0 = False |
HasAfterTrigger | Table, View | Table or view has an AFTER trigger. 1 = True0 = False |
HasInsertTrigger | Table, View | Table or view has an INSERT trigger. 1 = True0 = False |
HasInsteadOfTrigger | Table, View | Table or view has an INSTEAD OF trigger. 1 = True0 = False |
HasUpdateTrigger | Table, View | Table or view has an UPDATE trigger. 1 = True0 = False |
IsAnsiNullsOn | Function, Procedure, Table, Trigger, View | Specifies that the ANSI NULLS option setting for the table is ON, meaning all comparisons against a null value evaluate to UNKNOWN. This setting applies to all expressions in the table definition, including computed columns and constraints, for as long as the table exists. 1 = ON0 = OFF |
IsCheckCnst | Any | CHECK constraint. 1 = True0 = False |
IsConstraint | Any | Constraint. 1 = True0 = False |
IsDefault | Any | Bound default. 1 = True0 = False |
IsDefaultCnst | Any | DEFAULT constraint. 1 = True0 = False |
IsDeterministic | Function, View | The determinism property of the function. Applies only to scalar- and table-valued functions. 1 = Deterministic0 = Not DeterministicNULL = Not a scalar- or table-valued function, or invalid object ID. |
IsExecuted | Any | Specifies how this object can be executed (view, procedure, or trigger). 1 = True0 = False |
IsExtendedProc | Any | Extended procedure. 1 = True0 = False |
IsForeignKey | Any | FOREIGN KEY constraint. 1 = True0 = False |
IsIndexed | Table, View | A table or view with an index. |
IsIndexable | Table, View | A table or view on which an index may be created. |
IsInlineFunction | Function | Inline function. 1 = Inline function0 = Not inline functionNULL = Not a function, or invalid object ID. |
IsMSShipped | Any | An object created during installation of Microsoft® SQL Server™ 2000. 1 = True0 = False |
IsPrimaryKey | Any | PRIMARY KEY constraint. 1 = True0 = False |
IsProcedure | Any | Procedure. 1 = True0 = False |
IsQuotedIdentOn | Function, Procedure, Table, Trigger, View | Specifies that the quoted identifier setting for the table is ON, meaning double quotation marks delimit identifiers in all expressions involved in the table definition. 1 = ON0 = OFF |
IsReplProc | Any | Replication procedure. 1 = True0 = False |
IsRule | Any | Bound rule. 1 = True0 = False |
IsScalarFunction | Function | Scalar-valued function. 1 = Scalar-valued0 = Table-valuedNULL = Not a function, or invalid object ID. |
IsSchemaBound | Function, View | A schema bound function or view created with SCHEMABINDING. 1 = Schema-bound0 = Not schema-boundNULL = Not a function or a view, or invalid object ID. |
IsSystemTable | Table | System table. 1 = True0 = False |
IsTable | Table | Table. 1 = True0 = False |
IsTableFunction | Function | Table-valued function. 1 = Table-valued0 = Scalar-valuedNULL = Not a function, or invalid object ID. |
IsTrigger | Any | Trigger. 1 = True0 = False |
IsUniqueCnst | Any | UNIQUE constraint. 1 = True0 = False |
IsUserTable | Table | User-defined table. 1 = True0 = False |
IsView | View | View. 1 = True0 = False |
OwnerId | Any | Owner of the object. Nonnull = The database user ID of the object owner.NULL = Invalid input. |
TableDeleteTrigger | Table | Table has a DELETE trigger. >1 = ID of first trigger with given type. |
TableDeleteTriggerCount | Table | The table has the specified number of DELETE triggers. >1 = ID of first trigger with given type.NULL = Invalid input. |
TableFullTextBackground… | Table | The table has full-text background update index enabled. 1 = TRUE0 = FALSE |
TableFulltextCatalogId | Table | The ID of the full-text catalog in which the full-text index data for the table resides. Nonzero = Full-text catalog ID, associated with the unique index that identifies the rows in a full-text indexed table.0 = Table is not full-text indexed. |
TableFullTextChangeTr… | Table | The table has full-text change-tracking enabled. 1 = TRUE0 = FALSE |
TableFulltextKeyColumn | Table | The ID of the column associated with the single-column unique index that is participating in the full-text index definition. 0 = Table is not full-text indexed. |
TableFullTextPopulate… | Table | 0 = No population1 = Full population2 = Incremental population |
TableHasActiveFulltext… | Tables | The table has an active full-text index. 1 = True0 = False |
TableHasCheckCnst | Table | The table has a CHECK constraint. 1 = True0 = False |
TableHasClustIndex | Table | The table has a clustered index. 1 = True0 = False |
TableHasDefaultCnst | Table | The table has a DEFAULT constraint. 1 = True0 = False |
TableHasDeleteTrigger | Table | The table has a DELETE trigger. 1 = True0 = False |
TableHasForeignKey | Table | The table has a FOREIGN KEY constraint. 1 = True0 = False |
TableHasForeignRef | Table | Table is referenced by a FOREIGN KEY constraint. 1 = True0 = False |
TableHasIdentity | Table | The table has an identity column. 1 = True0 = False |
TableHasIndex | Table | The table has an index of any type. 1 = True0 = False |
TableHasInsertTrigger | Table | The object has an Insert trigger. 1 = True0 = FalseNULL = Invalid input. |
TableHasNonclustIndex | Table | The table has a nonclustered index. 1 = True0 = False |
TableHasPrimaryKey | Table | The table has a primary key. 1 = True0 = False |
TableHasRowGuidCol | Table | The table has a ROWGUIDCOL for a uniqueidentifier column. 1 = True0 = False |
TableHasTextImage | Table | The table has a text column. 1 = True0 = False |
TableHasTimestamp | Table | The table has a timestamp column. 1 = True0 = False |
TableHasUniqueCnst | Table | The table has a UNIQUE constraint. 1 = True0 = False |
TableHasUpdateTrigger | Table | The object has an Update trigger. 1 = True0 = False |
TableInsertTrigger | Table | The table has an INSERT trigger. >1 = ID of first trigger with given type. |
TableInsertTriggerCount | Table | The table has the specified number of INSERT triggers. >1 = ID of first trigger with given type. |
TableIsFake | Table | The table is not real. It is materialized internally on demand by SQL Server. 1 = True0 = False |
TableIsPinned | Table | The table is pinned to be held in the data cache. 1 = True0 = False |
TableTextInRowLimit | Table | The maximum bytes allowed for text in row, or 0 if text in row option is not set. |
TableUpdateTrigger | Table | The table has an UPDATE trigger. >1 = ID of first trigger with given type. |
TableUpdateTriggerCount | Table | The table has the specified number of UPDATE triggers. >1 = ID of first trigger with given type. |
Nota: Tabela retirada do site MSDN
A propriedade IsIndexable pode consumir significantes recursos de processamento.
A propriedade TableHasActiveFulltextIndex retornará 1 (Verdadeiro) quando no mínimo uma coluna da tabela é adicionada com indíce.
Vamos aos exemplos:
A. Para descobrir se um determinado objeto (Veiculo) é uma VIEW, basta executar a seguinte instrução
IF OBJECTPROPERTY(OBJECT_ID(‘Veiculo’), ‘ISVIEW’) = 1
PRINT ‘Veiculo é uma VIEW’
ELSE
PRINT ‘Veiculo não é uma VIEW’
Observe que utilizei a função OBJECT_ID, esta função retorna a identificação do objeto passado como parâmetro, que deve ser o nome do objeto na base de dados. Foi utilizado esta função para não haver a necessidade de descobrir a identificação do objeto.
Caso a necessidade seja outra, como por exemplo, saber se o objeto é uma tabela, basta substituir a palavra-chave ISVIEW por ISTABLE. Como no exemplo abaixo:
IF OBJECTPROPERTY(OBJECT_ID(‘Veiculo’), ‘ISTABLE’) = 1
PRINT ‘Veiculo é uma TABLE’
ELSE
PRINT ‘Veiculo não é uma TABLE’
B. Saber se uma determinada tabela possui um campo do tipo TEXT, retornará 1 quando for verdadeiro e 0 quando for falso. Uma variável do tipo booleana.
SELECT OBJECTPROPERTY(OBJECT_ID(‘Cliente’), ‘TableHasTextImage’)
C. Saber se uma determinada tabela possui uma chave-primária
SELECT OBJECTPROPERTY(OBJECT_ID(‘Fornecedor’), ‘TableHasPrimaryKey’)
D. Saber se uma determinada tabela possui uma TRIGGER SQL que remove (DELETE) registros
SELECT OBJECTPROPERTY(OBJECT_ID(‘Cliente’), ‘TableDeleteTrigger’)
E. Descobrir qual o proprietário de um determinado objeto, Ex.: PROCEDURE, TRIGGER, TABLE, etc.
SELECT OBJECTPROPERTY(OBJECT_ID(‘Veiculo’), ‘OwnerId’)
Esta função retorna a identificação do proprietário do objeto na base de dados ou NULL caso passado o parâmetro incorretamente.
Finalizo este artigo por aqui, deixe seu comentário, críticas e/ou sugestões.
A diferença entre DATALENGTH e LEN em SQL
novembro 1, 2007
A função DATALENGTH tem o objetivo retornar o número de bytes usados para representar uma determinada expressão. Esta função é geralmente utilizada em variáveis do tipo VARCHAR, VARBINARY, TEXT, IMAGE, NVARCHAR e NTEXT que são tipos de dados que podem armazenar grandes quantidades de dados.
Muitos confundem a função DATALENGTH com a função LEN, que são totalmente diferentes, podendo retornar os mesmos valores. A função LEN tem como intuito retornar a quantidade de caracteres existentes em uma determinada expressão.
SELECT
LEN(CPF) AS CPF
FROM
Cliente
A instrução acima mostrará a quantidade de caracteres existentes no campo ‘CPF’ da tabela ‘Cliente’.
SELECT
DATALENGTH(CPF) AS CPF
FROM
Cliente
Já esta outra consulta informa o número de bytes que o campo de um determinado registro está ocupando na base de dados. Quando o campo é NULL, ou seja, nulo em português, significa que o campo não tem valor nenhum e o retorno da função DATALENGTH é nulo enquanto o retorno da função LEN é 0, indicando que não possue nenhum caracter na expressão.
Identificando usuários em SQL
outubro 31, 2007
A palavra chave USER_NAME no SQL é usada para informar o nome do usuário (como o próprio nome diz). Veja o exemplo abaixo:
SELECT USER_NAME()
No caso acima informará o usuário que está logado na base de dados, se o usuário estiver em rede será informado o nome da rede, barra invertida e em seguida o nome do usuário. Ex.: ‘SQLCOMOUMTODO\alexandre’.
O USER_NAME pode receber um parâmetro que deve ser o ID do usuário na base de dados.
SELECT USER_NAME(1)
Na maioria dos casos, o ID 1 corresponde ao usuário ‘dbo’.
Para se obter maiores informações sobre o usuário, deve fazer o filtro na tabela de gerenciamento de usuários, a ‘sysusers’. Esta tabela pode fornecer informações como: quando foi criado o usuário (createdate), quando foi atualizado o usuário (updatedate), se o usuário está logado no momento, entre outras informações.
SELECT
*
FROM
sysusers
WHERE
name = USER_NAME(1)
A instrução acima retorna a mesma informação da instrução abaixo. Apenas mudando os campos que são comparados.
SELECT
*
FROM
sysusers
WHERE
uid = 1
Utilizando a claúsula TOP em SQL
outubro 30, 2007
Já expliquei aqui no blog como selecionar uma porcentagem de registros, mas não expliquei como funciona a claúsula TOP sem o PERCENT (indicativo de porcentagem).
O TOP sem o PERCENT retorna os primeiros registros conforme o número especificado após a clausula.
SELECT TOP 50
*
FROM
Cliente
A instrução acima irá informar os primeiros 50 clientes retornados na consulta. Muitos utilizam o TOP quando necessitam retornar o maior / menor registro de uma tabela, criando uma instrução similar a esta com um TOP 1 e ordenando por decrescente / ascendente para se obter o resultado. Há uma outra forma de se obter o mesmo resultado consumindo menos recursos, basta substituir pelo MAX / MIN, mas isto ficará para um próximo artigo.
Descobrindo se o campo é um número em SQL
outubro 29, 2007
A função ISNUMERIC do SQL informa se o campo passado por parâmetro é do tipo número, retornando uma variável booleana (verdadeiro ou falso). A sintaxe é simples e não possue nenhum segredo.
SELECT
ISNUMERIC(RG) AS [Número?]
FROM
Cliente
Esta função retorna uma variável booleana, porém em binário, indicando 0 quando for falso (não é número) e 1 quando for verdadeiro (número). É uma função muito útil que elimina a possibilidade de criar uma função no retorno do SQL.