Create a XML select from a table definition.

Rédigé par Sozezzo - - Aucun commentaire

Transform table definition to XML select, it can be useful when we need to create many XML select or re-create it.

Result:


SELECT

  [Message].value('(msg_Customers/Customers/CustomerID)[1]', 'nchar(5)') as [CustomerID]

, [Message].value('(msg_Customers/Customers/CompanyName)[1]', 'nvarchar(40)') as [CompanyName]

, [Message].value('(msg_Customers/Customers/ContactName)[1]', 'nvarchar(30)') as [ContactName]

, [Message].value('(msg_Customers/Customers/ContactTitle)[1]', 'nvarchar(30)') as [ContactTitle]

, [Message].value('(msg_Customers/Customers/Address)[1]', 'nvarchar(60)') as [Address]

, [Message].value('(msg_Customers/Customers/City)[1]', 'nvarchar(15)') as [City]

, [Message].value('(msg_Customers/Customers/Region)[1]', 'nvarchar(15)') as [Region]

, [Message].value('(msg_Customers/Customers/PostalCode)[1]', 'nvarchar(10)') as [PostalCode]

, [Message].value('(msg_Customers/Customers/Country)[1]', 'nvarchar(15)') as [Country]

, [Message].value('(msg_Customers/Customers/Phone)[1]', 'nvarchar(24)') as [Phone]

, [Message].value('(msg_Customers/Customers/Fax)[1]', 'nvarchar(24)') as [Fax]

FROM   [Customers]

WHERE  [Message].exist('/msg_Customers/Customers') = 1

 

Lire la suite de Create a XML select from a table definition.

Calculation SQL Table size

Rédigé par Sozezzo - - Aucun commentaire

We have many ways to obtain the size of all tables using SQL Server.

This is a nice solution but we do not really need to use temporary table.

http://therightstuff.de/2007/11/19/How-To-Obtain-The-Size-Of-All-Tables-In-A-SQL-Server-Database.aspx

If we run the script twice, we have an error.

Well, this version uses a variable and we have another solution.

Lire la suite de Calculation SQL Table size

Convert XML to table and table to XML

Rédigé par Sozezzo - - Aucun commentaire

We have a lot of articles about how to do but most of the time it is too much information.

This article tries to answer 2 questions!

How can we create XML column from a SQL query?
How can we create a table from XML column?

 

This example use NorthWind database:

https://northwinddatabase.codeplex.com/releases/view/71634

Lire la suite de Convert XML to table and table to XML

Liste de champs des tables

Rédigé par Sozezzo - - Aucun commentaire

Créer le même code à adapter à chaque table selon les champs et le type.

Cela peut être une activité assez plate. On peut créer un code qui crée notre code. Peut-on appeler une métacreation, ou métacodification, mais, peut-être, il n'existe pas ce mot en français.

Alors, ce SQL script nous liste les champs de tables, et après, c'est une autre histoire.

BEGIN TRY DROP TABLE #TMP END TRY BEGIN CATCH END CATCH;
SELECT
     TABLE_SCHEMA
    ,TABLE_NAME
    ,ORDINAL_POSITION AS rownumber
    ,COLUMN_NAME AS 'ColumnName'
    ,DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END AS 'DataType'
    ,IS_NULLABLE
INTO #TMP
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaSource AND TABLE_NAME = @TableSource;

Lire la suite de Liste de champs des tables

Finding Table and Primary Key and Foreign Key

Rédigé par Sozezzo - - Aucun commentaire

SQL Server does not help to easily determine the dependencies between tables.
This research presents the parent table and the child tables and columns used.

 

https://msdn.microsoft.com/en-us/library/ms179610.aspx
http://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server
 

well, however, we have many databases with bad design that didn’t have foreign keys defined but that did have related data.

Lire la suite de Finding Table and Primary Key and Foreign Key

Fil RSS des articles de cette catégorie