|
A modo de preludio Hace mucho, mucho tiempo (no digo 10 años porque mentiría, pero al menos cuatro si), que le vengo debiendo una nota a mi amigo Guille. Así que,. Aquí va, como regalito de cumpleaños. ¡¡¡FELICIDADES GUILLE!!! IntroducciónEn repetidas oportunidades, nos ha pasado que nos llaman para evaluar porqué una base de datos Sql Server “anda lento”… Y una de las cosas que mi amigo Eladio suele hacer es ponerse a “husmear” en las órdenes que le llegan al motor. Conclusión: se pierde MUCHO tiempo, obteniendo metadata de la base de datos. Ahora bien, ¿Qué es metadata? Hablamos de metadata para referirnos a la información que describe los elementos de la base. Por ejemplo, las columnas de una tabla, los parámetros de un procedimiento almacenado, etc. Veamos entonces como evitar este tipo de problemas. ¿Quién consulta la metadata?En realidad, muchas implementaciones tienen esa tendencia, basadas en la teoría de “mi código no se ve afectado si cambia la base, porque primero se fija que tiene la base”. Conceptualmente, para mí, esto en la mayoría de los casos, constituye un error de lógica. Si mi aplicación realiza tareas más allá del simple “leer y guardar registros”, inevitablemente cualquier cambio en la estructura de los datos, impactará en la lógica y por ello, se deberá modificar el código. Por ejemplo, es muy común ver que, antes de cargar datos para un objeto, se consultan las columnas que las tablas en las que están los datos contienen. Ahora bien, una vez obtenida esa lista de campos, ¿Qué se puede hacer? ¿Buscar uno por uno los campos que necesitamos a ver si existen? (Sí, lo hemos visto). Perfecto, y si uno no está, ¿Qué hacemos? Normalmente no queda otra que decirle al usuario ¿Sabes qué?, no puedes continuar porque hay un “error grave” y debe repararlo el programador… Exactamente lo mismo que vamos a decirle si, dentro de una estructura de control de errores, tratamos de acceder a una columna que ya no existe. Otra, bastante común, es ir a buscar los parámetros que tiene un procedimiento almacenado (lo cual, nobleza obliga, es algo que describí muy en detalle cómo hacer en alguna otra publicación mía). Pero siempre insistí que, de hacerse esto, debe PERSISTIRSE la información obtenida, para no consultar cada vez. Si me siguieron hasta aquí, ya estarán considerando que, sería interesante obtener la información al inicio de la aplicación, y a partir de allí utilizarla considerando que no cambia (cosa que en la gran, gran mayoría de los casos, es cierto). Veamos cómo podemos lograr esto, con el menor consumo de ancho de banda en la red, conexiones etc. Todo lo que necesitas se encuentra al final de INFORMATION_SCHEMA.Sql Server desde la versión 7, expone la estructura de la base de datos, a través de un conjunto de vistas que dependen de del esquema INFORMATION_SCHEMA. Consultando dichas vistas, es posible recuperar toda la información necesaria. Veamos pues, que se puede necesitar obtener. Desde la versión 2005, las tablas y demás objetos pueden depender de distintos esquemas (en realidad, antes también, pero dichos esquemas eran usuarios, cosa que ahora están como elementos independientes). La primer vista que deberíamos recuperar sería entonces INFORMATION_SCHEMA.Schemata Si ejecutamos select * from INFORMATION_SCHEMA.Schemata
veremos que nos trae una lista no sólo de los esquemas, sino también de los usuarios etc. Lo interesante comienza cuando relacionamos dicha vista con aquella que contiene las tablas: INFORMATION_SCHEMA.Tables. Si ejecutamos select * from Information_schema.tables
Podemos entonces combinar ambas en un INNER JOIN SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_TYPE FROM INFORMATION_SCHEMA.SCHEMATA INNER JOIN INFORMATION_SCHEMA.TABLES ON INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA ORDER BY INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME
Ahora bien, también tenemos otra vista que nos retorna las columnas de una tabla: INFORMATION_SCHEMA.Columns Agreguemos entonces un join con esta otra tabla SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_TYPE, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT, INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE, INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH, INFORMATION_SCHEMA.COLUMNS.CHARACTER_OCTET_LENGTH, INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION, INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE, INFORMATION_SCHEMA.COLUMNS.DATETIME_PRECISION FROM INFORMATION_SCHEMA.SCHEMATA INNER JOIN INFORMATION_SCHEMA.TABLES ON INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA INNER JOIN INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME ORDER BY INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
Llegamos a la parte interesante Ahora bien, ¿y si agregamos ? for XML AUTO,TYPE <INFORMATION_SCHEMA.SCHEMATA SCHEMA_NAME="dbo"> <INFORMATION_SCHEMA.TABLES TABLE_NAME="AWBuildVersion" TABLE_TYPE="BASE TABLE"> <INFORMATION_SCHEMA.COLUMNS COLUMN_NAME="SystemInformationID" ORDINAL_POSITION="1" IS_NULLABLE="NO" DATA_TYPE="tinyint" NUMERIC_PRECISION="3" NUMERIC_SCALE="0" /> <INFORMATION_SCHEMA.COLUMNS COLUMN_NAME="Database Version" ORDINAL_POSITION="2" IS_NULLABLE="NO" DATA_TYPE="nvarchar" CHARACTER_MAXIMUM_LENGTH="25" CHARACTER_OCTET_LENGTH="50" /> <INFORMATION_SCHEMA.COLUMNS COLUMN_NAME="VersionDate" ORDINAL_POSITION="3" IS_NULLABLE="NO" DATA_TYPE="datetime" DATETIME_PRECISION="3" /> <INFORMATION_SCHEMA.COLUMNS COLUMN_NAME="ModifiedDate" ORDINAL_POSITION="4" COLUMN_DEFAULT="(getdate())" IS_NULLABLE="NO" DATA_TYPE="datetime" DATETIME_PRECISION="3" /> </INFORMATION_SCHEMA.TABLES>
Ahora bien, si prestan atención al resultado de la consulta, ésta incluye no sólo las tablas, sino también las vistas de la base de datos. Armemos entonces dos consultas idénticas, pero filtrada por tipo (y de paso, quitemos el tipo en la consulta, que ya no nos sirve para nada) La primera llevaría: WHERE (INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'BASE TABLE')
WHERE (INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'VIEW')
Pero se trata simplemente de XML… armemos uno solo, armando un nodo para tablas y otro para vistas, que sería más o menos así: <Tablas>[consulta que arma el XML de las
tablas]</Tablas> El único problema aquí es de tipo de datos, ya que las consultas retornan el resultado como un solo campo de tipo xml. Pero para ello, Sql Server tiene funciones de conversión. J select '<Tablas>' + Convert(varchar(MAX), (SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT, INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE, INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH, INFORMATION_SCHEMA.COLUMNS.CHARACTER_OCTET_LENGTH, INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION, INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE, INFORMATION_SCHEMA.COLUMNS.DATETIME_PRECISION FROM INFORMATION_SCHEMA.SCHEMATA INNER JOIN INFORMATION_SCHEMA.TABLES ON INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA INNER JOIN INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME WHERE (INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'BASE TABLE') ORDER BY INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION for XML AUTO,TYPE )) +'</Tablas><Vistas>'+ Convert(varchar(MAX), (SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT, INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE, INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH, INFORMATION_SCHEMA.COLUMNS.CHARACTER_OCTET_LENGTH, INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION, INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE, INFORMATION_SCHEMA.COLUMNS.DATETIME_PRECISION FROM INFORMATION_SCHEMA.SCHEMATA INNER JOIN INFORMATION_SCHEMA.TABLES ON INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA INNER JOIN INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME WHERE (INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'VIEW') ORDER BY INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION for XML AUTO,TYPE )) +'</Vistas>'
Entonces, agreguemos un nodo, que abarque todo, cuyo nombre sea el nombre de la base, y de paso, hagamos que todo el resultado sea de tipo n xml: DECLARE @DBN varchar(200) --Si el nombre de la base tiene espacios los reemplaza set @DBN=replace(db_name(),' ','_') select convert(XML, '<'+@DBN+ '><Tablas>' + Convert(varchar(MAX), (SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT, INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE, INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH, INFORMATION_SCHEMA.COLUMNS.CHARACTER_OCTET_LENGTH, INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION, INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE, INFORMATION_SCHEMA.COLUMNS.DATETIME_PRECISION FROM INFORMATION_SCHEMA.SCHEMATA INNER JOIN INFORMATION_SCHEMA.TABLES ON INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA INNER JOIN INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME WHERE (INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'BASE TABLE') ORDER BY INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION for XML AUTO,TYPE )) +'</Tablas><Vistas>'+ Convert(varchar(MAX), (SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT, INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE, INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH, INFORMATION_SCHEMA.COLUMNS.CHARACTER_OCTET_LENGTH, INFORMATION_SCHEMA.COLUMNS.NUMERIC_PRECISION, INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE, INFORMATION_SCHEMA.COLUMNS.DATETIME_PRECISION FROM INFORMATION_SCHEMA.SCHEMATA INNER JOIN INFORMATION_SCHEMA.TABLES ON INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA INNER JOIN INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME WHERE (INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'VIEW') ORDER BY INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION for XML AUTO,TYPE )) +'</Vistas></' +@DBN +'>') Te dejo la inquietudUna de las cosas que no me gusta es dejar todo servido. Porque así, no aprendes J Entonces, fíjate de resolver esto: 1) La verdadera consulta que yo uso, incluye los procedimientos almacenados y sus parámetros 2) También incluye por cada columna, si ésta depende de una relación de integridad o una referencia, con que tabla y con que columna de esa otra tabla es. 3) De cada uno de los elementos, es posible también agregar la descripción que se encuentra en la base para dicho objeto. Pistas. · INFORMATION_SCHEMA.ROUTINES · INFORMATION_SCHEMA.PARAMETERS · INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS · INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE · fn_listextendedproperty('MS_Description',… Ojalá te sea útil. Dani O Daniel Seara Director de Tecnología de Desarrollo de Sistemas Solid Quality Mentors www.solidqualitylearning.com Si es que la quieren “seria”
|
