Sql, curioseando

Unas utilidades para conocer la estructura de una base de datos de sqlserver ,

Obtener las bases de datos de un servidor

select name as descripcion
from sys.databases
WHERE name not like 'EXTRA%'

Obtener todas las tablas o vistas de un servidor

select name, object_id ,create_date from sys.tables order by name

select name , object_id ,create_date from sys.views order by name

Obtener todas las columnas de un todas las tablas o vistas de una bbdd

select t.object_id, c.name,system_type_id, max_length, c.precision , scale ,is_nullable, c.column_id from sys.columns c ,
sys.tables t where t.object_id= c.object_id

select t.object_id, c.name,system_type_id, max_length, c.precision , scale ,is_nullable,c.column_id from sys.columns c ,
sys.views t where t.object_id= c.object_id
order by t.OBJECT_ID, c.column_id

Obtener las columas que forman parte de todos los índices de una base de datos

select t.TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as c
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS as t on
t.CONSTRAINT_NAME = c.CONSTRAINT_NAME
where t.CONSTRAINT_TYPE='PRIMARY KEY'
order by TABLE_NAME

Obtener los trigger de una bbdd

select t.object_id, name, e.type, e.type_desc, t.parent_id
from sys.triggers t, sys.trigger_events e
where t.type ='TR' and t.object_id=e.object_id

Obtener todos los SP , funciones de una bbdd

select name, object_id from sys.objects where type in ('FN' ,'P', 'TF', '[IF]') order by name
Obtener todos los parámetros de entrada de todos los sp de una base de datos
SELECT SPECIFIC_NAME, PARAMETER_NAME, data_type, CHARACTER_MAXIMUM_LENGTH , NUMERIC_PRECISION, NUMERIC_SCALE, max_length , is_nullable
FROM INFORMATION_SCHEMA.PARAMETERS, sys.types
WHERE PARAMETER_MODE='IN' and INFORMATION_SCHEMA.PARAMETERS.DATA_TYPE =sys.types.name
ORDER BY SPECIFIC_NAME, ORDINAL_POSITION

Buscar texto dentro de un procedimiento o función de sql

-- @texto -->texto a buscar
--@BBDD --> nombre de la base de datos, si no se pasa ninguno busca en todas las bases de datos del servidor
Create PROCEDURE [dbo].[Find_Text](@texto varchar(50), @BBDD varchar(75)= null) as
begin

declare @ssQL nvarchar(max)
declare @nombreBBDD varchar(75)
declare @sWhere varchar(max)
declare @texo_find varchar(75)
declare @index_Final int
declare @tablaSP table
(
name_SP varchar(500),
name_BBDD varchar(500),
Tipo varchar(10),
ID INT
)
declare @tablaBasesDatos table
(
nombreBBDD varchar(75)
)

INSERT INTO @tablaBasesDatos
select db.name from sys.databases db WHERE DB.name = ISNULL(@BBDD,DB.NAME) and DB.name not in('msdb','DatabaseControl','master','model','tempdb')
order by db.database_id

set @sWhere = 'where '
if CHARINDEX(',',@texto)=0
begin
set @sWhere = @sWhere+ ' sc.text like ''%'+@texto+'%'' '
end
else
begin
WHILE CHARINDEX(',',@texto,0) > 0
begin
Set @index_Final= CHARINDEX(',',@texto,0)+1
set @texo_find= substring(@texto,0, @index_Final)
set @texto= REPLACE(@texto,@texo_find,'')
set @sWhere = @sWhere+ ' sc.text like ''%'+ ltrim(rtrim(replace( @texo_find,',',''))) +'%'' OR '
end
if @texto<> ''
BEGIN
set @sWhere = @sWhere+ ' sc.text like ''%'+ ltrim(rtrim( @texto)) +'%'' '
END
end

WHILE EXISTS (SELECT * FROM @tablaBasesDatos)
BEGIN

SELECT top 1 @nombreBBDD = nombreBBDD FROM @tablaBasesDatos

set @ssQL='select DISTINCT so.name ,'''+@nombreBBDD+''', so.XTYPE, sc.ID
from ' + @nombreBBDD + '..sysobjects so inner join ' + @nombreBBDD + '..syscomments sc on so.id = sc.id AND sc.encrypted= 0 '+ @sWhere

BEGIN TRY
INSERT INTO @tablaSP
execute sp_executesql @ssQL
END TRY
BEGIN CATCH
END CATCH;
DELETE TOP (1) @tablaBasesDatos
END

SELECT name_SP,
CASE WHEN Tipo='FN' THEN 'F. ESCALAR'
WHEN Tipo='P' THEN 'PROCEDIMIENTO'
WHEN Tipo='TF' THEN 'F. TABLA' ELSE Tipo END as tipo
,name_BBDD , ID FROM @tablaSP order by name_BBDD,Tipo,name_SP

end

Ojo cuidado cuando se usa el syscomments !!!! Ejemplo de un "problemilla"

Creamos un procedimiento : nombre1 ( esto aparece en el syscomments sin problemas)
Cambiamos el nombre del procedimiento: pulsando sobre el nombre,boton derecho "Cambiar Nombre" y ponemos el nuevo nombre por "NEW _NOMBRE"
Ahora mira el syscomments !!! pues si en el syscomments en el texto del SP está el nombre viejo.
Está documentado http://support.microsoft.com/kb/243198
"Al cambiar el nombre de un procedimiento almacenado, una vista o un desencadenador no se actualiza la tabla SYSCOMMENTS"

Variables de SQL Server

Para controlar el anidamiento de SQL server podemos usar la variable
@@NESTLEVEL

MSDN
Ejemplo de uso, muy útil Ejemplo

The "GenerateResource" task failed unexpectedly.

Este ha sido un error, raro.
Una solución con dos proyectos: un proyecto de escritorio y una dll .net 2010 , Vb .net, framework 4

Cuando compilaba, algunas veces (no siempre) daba un error
The "GenerateResource" task failed unexpectedly

Para solucionarlo abría un par de formularios, limpiaba la solución y generaba y compilaba.

Esto es "pasable" si solo lo tengo que hacer una vez, pero no era mi caso. Buscando encontré una solución que me valía :

Modificar (con el bloc de notas) el fichero .vbproj y añadir
<generateresourceneverlocktypeassemblies>true</generateresourceneverlocktypeassemblies>

Ejemplo

<Code>
<project defaulttargets="Build" toolsversion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<propertygroup>
<generateresourceneverlocktypeassemblies>true</generateresourceneverlocktypeassemblies>
</propertygroup>

</code>

Un error similar con una explicación y solución diferente

Curiseando Dispose & Finalize

Hoy mirando el código de otro (lo juro ) tenía una cosa como está

Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
Dim aux As MiClase
aux = funcion()
MessageBox.Show(aux.NewProperty)
MessageBox.Show(aux.OtraPropiedad)
End Sub

Public Function funcion() As MiClase
Dim oRet As MiClase = New MiClase()
Dim oRet2 As MiClase = oRet
Try
oRet.NewProperty = "otro valor"
Return oRet
Catch ex As Exception
Return Nothing
Finally
oRet.Dispose()
oRet = Nothing
End Try
End Function

Yo, que mi orígenes fueron el c y el c++ (no todo tiempo pasado fue mejor), siempre entiendo que el “dispose” libera todos objetos de la clase . Por supuesto sé que el Finally se ejecuta.

La pregunta de hoy es: qué valor tiene aux.NewProperty y aux.OtraPropiedad?.
Para dar alguna pista (o confundir más al lector) dejo la clase :

Public Class MiClase
Private newPropertyValue As String
Private _otraPropiedad As String

Public Property OtraPropiedad() As String
Get
Return _otraPropiedad
End Get
Set(ByVal value As String)
_otraPropiedad = value
End Set
End Property

Public Property NewProperty() As String
Get
Return newPropertyValue
End Get
Set(ByVal value As String)
newPropertyValue = value
End Set
End Property
Public Sub New()
newPropertyValue = "mi valor"
_otraPropiedad = "mi 2 valor"
End Sub

Public Sub Dispose()
_otraPropiedad = String.Empty
End Sub

End Class

La respuesta es: aux tiene valor. El return de la función ha retornado el puntero y la memoria realmente no se ha liberado. Dejo la captura del debug.

Ya puestos en la “funcion” he puesto un código que no “vale para nada”, solo para ver que realmente se usan punteros aunque no lo veamos

Dim oRet2 As MiClase = oRet

La pregunta es : ¿qué vale oRet2 después de ejecutar oRet = Nothing?. Le pasa exatamente lo mismo que al aux: todo son punteros

En C# funciona exactamente igual

IIF VB .net

Un programador C .net puede pensar que la instrucción iif de vb es equivalente al operador de compración ?
Una sorpresa: ¿que crees que saldrá en el MessageBox?

Dim total As Integer = 0
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
Dim a As Integer = 1
Dim b As Integer = 2
Dim c As Boolean = IIf(a = b, funcion1("b"), funcion2("1"))
MessageBox.Show(total)
End Sub
Public Function funcion1(ByVal valor As String) As Boolean
total = total + 1
Return True
End Function
Public Function funcion2(ByVal valor As String) As Boolean
total = total + 1
Return True
End Function

2 Imagina que hicieras otra cosa:, consultas a base de datos, mostrar MessageBox , como ejecuta las dos funciones si una de ellas da Exception aunque nunca se cumpliera la condición para su llamada fallaría…

Y resulta que está documentado http://msdn.microsoft.com/es-es/library/27ydhh0d%28v=vs.80%29.aspx

“El IIF evalúa el código a ejecutar (tanto si la condición es verdadera o falsa) antes de ejecutarlo (esto no sé si es fallo de MS o es intencional). El problema que podemos encontrar con esto es que hay que asegurarse que las sentencias a ejecutar sean válidas en todas las condiciones, si no tendremos un excepción.”


Si tienes curiosidad C#, hace lo que esperas (o por lo menos lo que yo esero: el valor de total es 1

int total = 0;
private void button1_Click(object sender, EventArgs e)
{
int a = 1;
int b = 2;
bool c = (a == b ? funcion1("b") : funcion2("1"));
MessageBox.Show(total.ToString());
}
private bool funcion1(String valor)
{
total = total + 1;
return true;
}
private bool funcion2(String valor)
{
total = total + 1;
return true;
}

Indices base datos

El minuto de hoy: Tengo una consulta que tarda más de 3min. Ejecutando el plan de ejecución me dice que debería tener un índice más (hasta aquí todo normal).
Cuando veo el índice que me sugiere :

CREATE NONCLUSTERED INDEX [miindice]
ON [dbo].[mitabla] ([codigo],[nombre])
INCLUDE ([fecha], [cantidad])

Qué es esto??
Vaya hasta el momento (para mi) un índice era un conjunto de columnas. Resulta que ya no es así
existe el crear índices con columnas incluidas
MSDN
Por cierto con el índice la consulta mejoro en más de un 50% el tiempo

Servidores Vinculados SQLServer

Bueno últimamente nos ha tocado trabajar con servidores vinculados

Ambos servidores se ven:
En servidor1 está mibase1 donde esta mitabla1 con su columna nombredoc1 --> no es case-sensitive
En servidor2 está mibase2  donde esta mitabla2 con su columna nombredoc2 --> es case-sensitive

Si en servidor1 ejecuto

select top 1  NOMBREDOC2  from servidor2. mibase2 .dbo. mitabla2 --> ok
select top 1  nombredoc2  from servidor2. mibase2.dbo. mitabla2 --> ok

Si en servidor2 hago

select top 1  NOMBREDOC1  from servidor1. Mibase1 .dbo. mitabla1 -->  error
select top 1  nombredoc1  from servidor1. Mibase1.dbo. mitabla1--> ok

Lo ha configurado para que sea case-sensitive hasta el nombre de las columnas!!!!

La configuración es importante, pero si lo pones a nivel de estructura mira las implicaciones (sobre todo en una migración)

Pregunta es ¿Si se puede tener la BD definida como  case sensible y consultas NO?

Respuesta es :  hay que crear la BD como case no sensible y tablas columnas como sensibles


http://msdn.microsoft.com/en-us/library/ms143508(v=sql.105).aspx


SQL Server 2008 supports setting collations at the following levels:
•         Server
•         Database
•         Column
•         Expression


http://msdn.microsoft.com/en-us/library/ms187053(v=sql.90).aspx

Parameters for stored procedures or functions, alias data types, and variables are assigned the default collation of the database. To change the collation of an alias type, you must drop and re-create it.

Problemas de rendimiento

Servidor 2

select distinct a.fecha , a.nif ,
b.nombre  from basedatos2.dbo.tabla2 a ,servidor1.base1.dbo.tabla1 b
where a.nif = b.nif   and a.fecha = '08/10/2012' order by b.nombre asc --> 6sg

Servidor 1

select distinct a.fecha , a.nif ,
b.nombre  from servidor2.basedatos2.dbo.tabla2 a , base1.dbo.tabla1 b
where a.nif = b.nif  and a.fecha = '08/10/2012' order by b.nombre asc --> IMEDIATO

Para que vaya rápido en el servidor2

select distinct a.fecha , a.nif ,
b.nombre  from basedatos2.dbo.tabla2 a , openquery(servidor1, 'select * from base1.dbo.tabla1')  b
where a.nif = b.nif   and a.fecha = '08/10/2012' order by b.nombre asc -->INMEDIATO

Las soluciones posibles :
- mejorar las estructura de la consulta, (índices,  openquery)
- replicar la base de datos  Replicar las base de datos ( http://www.sqlservercentral.com/articles/Linked+Server/62246/)

La "chapu" para cuando vas fatal de tiempo  es aumentar el tiempo de time-out de las consultas de tu código