Ms Sql Server Technology Discussions

[deals2buy]

[quote name='GatisKandis' timestamp='1360717751' post='1303269132']
flow chart easy ga dhorukutundhi msdn lo ne undhi kani mari first ninchi rayadam avasaram ledhu anukunta....naaku plan undhi kanukoni weekend lopu cheptha [b][color=#ff0000]kalisi rasi 100% kodudham[/color][/b] [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[/quote]
raaju vayya maha raaju vayya [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[quote name='masi_boggu' timestamp='1360718875' post='1303269214']
MCTS- 70448 ( sql server 2008 business intelligence development & maintenance)
[/quote]thanks mama

alaage nee daggara emaina dumps kuda unte ivvu koddiga

[ChampakDas]

racha mama thanks...
[quote name='masi_boggu' timestamp='1360718875' post='1303269214']
MCTS- 70448 ( sql server 2008 business intelligence development & maintenance)
[/quote]

no dumps full reading[img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[quote name='Kaarthikeya' timestamp='1360720706' post='1303269342']
raaju vayya maha raaju vayya [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
thanks mama

alaage nee daggara emaina dumps kuda unte ivvu koddiga
[/quote]

[deals2buy]

[quote name='GatisKandis' timestamp='1360764037' post='1303270802']

no dumps full reading[img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[/quote]
full reading aithe next year raayalemo [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]

[ChampakDas]

slow and steady wins the certifications [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[quote name='Kaarthikeya' timestamp='1360766006' post='1303270923']
full reading aithe next year raayalemo [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[/quote]

[deals2buy]

[quote name='GatisKandis' timestamp='1360766554' post='1303270995']
slow and steady wins the certifications [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[/quote]
then lets istart [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]

[ChampakDas]

vakkay...samsara sagaramu sanginchu rama...[img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[quote name='Kaarthikeya' timestamp='1360767345' post='1303271079']
then lets istart [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[/quote]

[deals2buy]

[quote name='GatisKandis' timestamp='1360768183' post='1303271183']
vakkay...samsara sagaramu sanginchu rama...[img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[/quote]
lets start with one topic a day then [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]

[ghazni]

Asalu first a certification start chestunaru idar bolo nenu be try chesta.........

[ChampakDas]

eroju repu break edem repu 14th neeke problem [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]
[quote name='Kaarthikeya' timestamp='1360768594' post='1303271241']
lets start with one topic a day then [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]

[/quote]


not yet planned...aa complete flow chart patukuntaruvatha mundhuki podham...over to kaarthikeya
[quote name='ghazni' timestamp='1360768746' post='1303271265']
Asalu first a certification start chestunaru idar bolo nenu be try chesta.........
[/quote]

[deals2buy]

[quote name='ghazni' timestamp='1360768746' post='1303271265']
Asalu first a certification start chestunaru idar bolo nenu be try chesta.........
[/quote]
raasthe BI certification raaddamm...adaithe manaki anni vidhaala useful anukunna
[quote name='GatisKandis' timestamp='1360769275' post='1303271326']
eroju repu break edem repu 14th neeke problem [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]



not yet planned...aa complete flow chart patukuntaruvatha mundhuki podham...over to kaarthikeya
[/quote]

nenu repu samaadhi lo ki veltunna..malli ellundi vastha [img]http://www.manadb.com/Smileys/default/Brahmi.gif[/img]

[deals2buy]

[b] Get SQL Server Version and Edition Information[/b]


What do consultants do when they come across any new instance of SQL Server? Well, their very first question is what version of SQL Server is it? The reason is simple – SQL Server is a very vast product and each version of the product have new features released and old features deprecated. Many consultant even remembers service pack and features released in it.

Well, there are multiple ways to know the version numbers of the SQL Server. In this sixty second video we will see a neat trick where we will quickly find the version number of SQL Server.

Let us see the same concept in following SQL in Sixty Seconds Video:

http://www.youtube.com/watch?feature=player_embedded&v=8P5TuOg3PlA

[deals2buy]

[b] Moving Database Files in SQL Server[/b]

Under SQL Server 2000, altering database paths was all but limited to the TempDB database. You had to detach the user database and then re-attach it, or you could use backup, then restore. Hmm, messy to say the least.

Under normal operation, once SQL Server has a handle on a database you will not be able to manipulate the files at the OS level. Any attempt to copy the files, etc., will result in the dialog box below.

[img]http://www.sqlservercentral.com/Images/17263.jpg[/img]

To address the first point, thankfully in SQL Server 2005 onwards, this is no longer necessary, and in fact SP_ATTACH_DB has been deprecated and will be removed in a future version of SQL Server. You should now use:
[CODE]CREATE DATABASE ... FOR ATTACH[/CODE]
To address the second point, in order to release the handle the database engine has on the user database files we merely need to Offline the database. We [b][u]do[/u] [u]not[/u][/b] need to stop the SQL Server services.
Let's just re-cap that; we [b][u]do[/u] [u]not[/u][/b] need to stop the SQL Server services.
You may issue the following command to Offline the database;
ALTER DATABASE [yourDB] SET OFFLINE
If you have active connections and wish to roll them back and take the database offline you may do so using;
[CODE]ALTER DATABASE [yourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE[/CODE]
Once the database is Offline you may move and\or rename your database files. Just remember that if you delete or rename the files, the database will not come back online again. When attempting to Online the database you will usually receive an error along the lines of:

[img]http://www.sqlservercentral.com/Images/17269.jpg[/img]

You must first amend the system catalogs to provide the new paths\filenames, this is done using the ALTER DATABASE command passing in the MODIFY FILE parameters as shown in the following query construct:
[CODE]ALTER DATABASE [yourDB]
MODIFY FILE ( name=logicalfilename,
filename=N'c:\folder1\folder2\adbfile.mdf'
)[/CODE]
[b]Important Note:[/b] When using the T-SQL command above, SQL Server will accept whatever you type and issue in the ALTER DATABASE statement so be careful and check your typing!
For example this would be valid
[CODE]ALTER DATABASE [yourDB] MODIFY FILE(name=logicalfilename, filename=N'c:\MSSQL\DATA\gobbeldygook.dat')[/CODE]
If the path\filename does not exist when the database tries to start you will receive an error!!
[b] Moving the Files[/b]

With the above in mind, let's look at how we would achieve the goal of moving a databases disk files to new locations.
[b] Our Scenario[/b]

The server drives are filling up quickly and you have been asked by the manager to move the disk files to a new set of drives provided by the Windows administrator. The engineer has created your new file paths for you and retained all NTFS ACLs required for the SQL Server services. The drives\paths supplied are as follows (I am using my C drive but this could easily be G or F or some other drive letter);
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBA\MSSQL\Data

Before making any changes to the OS files and their locations first check the metadata available to you in the following system catalog: master.sys.master_files. The important metadata to collect consists of the Logical Filenames and the Physical Names. You may obtain this information using the following query;
[CODE]SELECT database_id,
name,
physical_name
FROM sys.master_files
WHERE database_id = DB_ID('SampleServiceCompany2009')[/CODE]
For my database I have the following:


[img]http://www.sqlservercentral.com/Images/17264.jpg[/img]
[img]http://www.sqlservercentral.com/Resources/Images/zoom.gif[/img][url=""]Zoom in[/url] | [url=""]Open in new window[/url]


I need to amend these paths from the "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory to match the new location provided to me. The first task is to construct a set of T-SQL move commands. The ALTER DATABASE ... MODIFY FILE command really only needs 2 parameters for the file move: the files logical name and the physical_name. Both of these are retrieved in the query shown above. The move commands are extremely simple and as shown earlier take the following form;


[img]http://www.sqlservercentral.com/Images/17265.jpg[/img]
[img]http://www.sqlservercentral.com/Resources/Images/zoom.gif[/img][url=""]Zoom in[/url] | [url=""]Open in new window[/url]

Issuing these commands now will update the system catalogs, however, the new paths will [b][u]not[/u][/b] take effect until the database has been restarted (or taken Offline then brought back Online). The following message informs us of this:


[img]http://www.sqlservercentral.com/Images/17266.jpg[/img]
[img]http://www.sqlservercentral.com/Resources/Images/zoom.gif[/img][url=""]Zoom in[/url] | [url=""]Open in new window[/url]

I'll now take the database Offline using the command highlighted below.

[img]http://www.sqlservercentral.com/Images/17267.jpg[/img]

With the database Offline, I may now move the files(and even rename them if I really wanted to).

A word of caution here. It is advisable to copy and paste the files to the new locations. Only when the database comes online successfully would I then remove the old files.

Once you have copied the files to the new locations you would then bring the database Online, this is done using:
[CODE]ALTER DATABASE [yourDB] SET ONLINE[/CODE]
Shown below are the typical screenshots you will see if the database fails to start. From the information dialog below click the message column and you will see details of the issue.

[img]http://www.sqlservercentral.com/Images/17268.jpg[/img]

The error details show below provide an indication to the issue, the files probably do not exist (in fact that's generally exactly what an OS Error 2 means).

[img]http://www.sqlservercentral.com/Images/17269.jpg[/img]

If the database starts successfully you will see the following

[img]http://www.sqlservercentral.com/Images/17270.jpg[/img]

[b] Help, My Database Won't Start[/b]

In every scenario I have encountered whereby a database file move has failed, the issue has been down to a mistyped path and\or filename, resulting in the DBA then getting into a vicious loop trying to correct themselves. Should your database fail to start, don't panic. Perform the following tasks;[list]
[*]Check the script you used to modify the database file locations, have you got the paths and filenames correct?
[*]Have your admin check the permissions to the new path for the SQL Server Database Engine service account.
[*]Query the catalog master.sys.master_files , do the paths\filenames here match what you're expecting?
[*]If you are unable to complete the move successfully, revert back to the original file paths\names. As you left the files in the source directory simply issue the appropriate ALTER DATABASE ... MODIFY FILE statements and bring the database back online.
[/list]
Querying master.sys.master_files to obtain the current database file paths

[img]http://www.sqlservercentral.com/Images/17271.jpg[/img]


This is a very easy task to complete providing you pay full attention to the task in hand. If you encounter an error, stop and review what you currently have in place both from the system catalog metadata and the physical OS locations\filenames.

[deals2buy]

[b] Move all SQL Server indexed views to a new filegroup[/b]

[b] Problem[/b]

As databases grow, it often becomes necessary to add new I/O paths to accommodate the growing space. Even without growth that requires this scale, it can still be useful to utilize multiple I/O devices to spread out the load. One way that you can make optimal use of new drives is to add a filegroup to the database and move certain objects (say, all of your indexed views) to the new filegroup. (In fact, you could make a case that you should move all user objects from the PRIMARY filegroup, but I will save that for another post.)
[b] Solution[/b]

Moving a single indexed view to a new filegroup is quite simple. Well, really, you move the clustered index, which - for all intents and purposes - is the data that the view materializes. So we just need to take the definition of the view, and add the WITH DROP_EXISTING and ON [FileGroup_Name] options.
Quick example:
[CODE]CREATE UNIQUE CLUSTERED INDEX UQ_ViewName_ColumnName
ON dbo.View_Name(Column_Name);
GO[/CODE]

Becomes:
[CODE]CREATE UNIQUE CLUSTERED INDEX UQ_ViewName_ColumnName
ON dbo.View_Name(Column_Name)
WITH (DROP_EXISTING = ON)
ON [FileGroup_Name];
GO[/CODE]
You can also optionally add ONLINE = ON if you are running Developer or Enterprise Edition. But what if you have a large number of views? What if your indexed views also have multiple non-clustered indexes? What if you want to schedule this work for a maintenance window?
One way to do this would be very tedious:[list=1]
[*]In Management Studio's Object Explorer, open up your database's Views node
[*]For each view, expand Indexes and check if the view has an index
[*]Right-click each index, choose Script View As > CREATE To > some location (as long as Tools > Options > SQL Server Object Explorer > Script Indexes is set to true, not the default - otherwise, repeat for each index)
[*]Remove the CREATE VIEW and other code not relating specifically to the index(es)
[*]Add the DROP_EXISTING and ON [FileGroup_Name] options
[*]Execute the script or save it somewhere for later execution
[*]Goto 1
[/list]
Another way would be similarly tedious. With Views highlighted in Object Explorer, open Object Explorer Details (F7). Unlike Object Explorer, Object Explorer Details allows you to select multiple objects, so that you can perform the same operation against many items at the same time. From here you can select all of the views (or a subset, if you know which views have indexes or not - unfortunately this view does not offer "Has Index" or "Number of Indexes" as an exposed property). Then you can right-click and perform steps 3-6 above just once, instead of once per object.
[b] A Better Way[/b]

Management Studio doesn't invent all of this data that it outputs through the scripting options; it gets the information from the catalog views. Well, so can we, and we can generate the precise scripts we'll need to recreate these indexes dynamically . Let's say we have the following objects, and we've added a new filegroup:
[CODE]USE master;
GO
CREATE DATABASE ivtest;
GO
USE ivtest;
GO
CREATE TABLE dbo.Sample1(a INT);
GO
ALTER VIEW dbo.Sample1_View
WITH SCHEMABINDING
AS
SELECT a, c = COUNT_BIG(*)
FROM dbo.Sample1
GROUP BY a;
GO
CREATE UNIQUE CLUSTERED INDEX PK_Sample1 ON dbo.Sample1_View(a);
GO
CREATE TABLE dbo.Sample2(a INT, b INT, c INT);
GO
CREATE VIEW dbo.Sample2_View
WITH SCHEMABINDING
AS
SELECT a, b, c, d = COUNT_BIG(*)
FROM dbo.Sample2
GROUP BY a, b, c;
GO
CREATE UNIQUE CLUSTERED INDEX PK_Sample2 ON dbo.Sample2_View(a);
GO
CREATE INDEX IX_Sample2_d__a ON dbo.Sample2_View(d,a)
CREATE INDEX IX_Sample2_b_a__c ON dbo.Sample2_View(b,a) INCLUDE(c);
CREATE INDEX IX_Sample2_d__c_a ON dbo.Sample2_View(d) INCLUDE(c,a);
GO
ALTER DATABASE ivtest ADD FILEGROUP Indexed_Views;
GO
ALTER DATABASE ivtest ADD FILE (name=N'Indexed_Views', filename=N'C:\temp\ivtest_iv.ndf')
TO FILEGROUP [Indexed_Views];
GO[/CODE]
To find all of the views with at least one index, and each index that hasn't already been moved to the Indexed_Views filegroup:
[CODE]SELECT [view] = v.name, [index] = i.name
FROM sys.views AS v
INNER JOIN sys.indexes AS i
ON v.[object_id] = i.[object_id]
INNER JOIN sys.filegroups AS f
ON f.data_space_id = i.data_space_id
WHERE f.name <> N'Indexed_Views'
ORDER BY v.namem, i.index_id;[/CODE]
This returns:
[CODE]view index
------------ -----------------
Sample1_View PK_Sample1
Sample2_View PK_Sample2
Sample2_View IX_Sample2_d__a
Sample2_View IX_Sample2_b_a__c
Sample2_View IX_Sample2_d__c_a[/CODE]
We also need to track down the columns in each index and, for non-clustered indexes, whether the column is a key or include column. The order of the key columns is also important. So we can add in a few columns to get the additional information:
[CODE]SELECT
[view] = v.name,
[index] = i.name,
[column] = c.name,
[include] = ic.is_included_column
FROM sys.views AS v
INNER JOIN sys.indexes AS i
ON v.[object_id] = i.[object_id]
INNER JOIN sys.columns AS c
ON c.[object_id] = v.[object_id]
INNER JOIN sys.index_columns AS ic
ON c.[object_id] = ic.[object_id]
AND c.column_id = ic.column_id
AND ic.index_id = i.index_id
INNER JOIN sys.filegroups AS f
ON f.data_space_id = i.data_space_id
WHERE f.name <> N'Indexed_Views'
ORDER BY v.name, i.index_id, ic.index_column_id;[/CODE]
Results:
[CODE]view index column include
------------ ----------------- ------ -------
Sample1_View PK_Sample1 a 0
Sample2_View PK_Sample2 a 0
Sample2_View IX_Sample2_d__a d 0
Sample2_View IX_Sample2_d__a a 0
Sample2_View IX_Sample2_b_a__c b 0
Sample2_View IX_Sample2_b_a__c a 0
Sample2_View IX_Sample2_b_a__c c 1
Sample2_View IX_Sample2_d__c_a d 0
Sample2_View IX_Sample2_d__c_a c 1
Sample2_View IX_Sample2_d__c_a a 1[/CODE]

This seems to be all of the information we need to re-create these indexes, assuming that the rest of the default options were in use at the time (and the defaults haven't been changed). You may want to add columns to this output in order to use in the dynamic output so that those settings are maintained. Now, in order to generate a script that you don't have to run manually, I use a dynamic SQL technique that I employ often. It just uses a variable to append each line of code I want to execute, drawn on the results of the join above. Since an index can have multiple key or include columns, I use a concatenation trick with FOR XML PATH in a subquery in order to build the comma-separated list of columns.

The code looks like this:
[CODE]SET NOCOUNT ON;
DECLARE @opt NVARCHAR(13), @sql NVARCHAR(MAX), @fg NVARCHAR(128);
SELECT
@fg = N'Indexed_Views', -- you'll want to change this to your FG name
@sql = N'', -- important to initialize this!
@opt = CASE WHEN CONVERT(NCHAR(3), SERVERPROPERTY('Edition'))
IN (N'Ent', N'Dev') THEN N', ONLINE = ON' ELSE N'' END;
SELECT @sql = @sql + N'
CREATE ' + CASE WHEN i.index_id = 1
THEN 'UNIQUE CLUSTERED'
ELSE '' END
+ ' INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(SCHEMA_NAME(v.[schema_id]))
+ '.' + QUOTENAME(v.name)
/*
comma-separated list of key columns, ordered
*/
+ '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
FROM sys.columns AS c
INNER JOIN sys.index_columns AS ic
ON c.[object_id] = ic.[object_id]
AND c.column_id = ic.column_id
WHERE c.[object_id] = v.[object_id]
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.index_column_id
FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
+ ') '

/*
comma-separated list of include columns, if necessary
(order is not important)
*/

+ COALESCE(' INCLUDE (' + STUFF((SELECT ',' + QUOTENAME(c.name)
FROM sys.columns AS c
INNER JOIN sys.index_columns AS ic
ON c.[object_id] = ic.[object_id]
AND c.column_id = ic.column_id
WHERE c.[object_id] = v.[object_id]
AND ic.index_id = i.index_id
AND ic.is_included_column = 1
FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
+ ')', '') + '
WITH (DROP_EXISTING = ON' + @opt + ')
ON ' + QUOTENAME(@fg) + ';'
FROM sys.views AS v
INNER JOIN sys.indexes AS i
ON v.[object_id] = i.[object_id]
INNER JOIN sys.filegroups AS f
ON f.data_space_id = i.data_space_id
WHERE f.name <> 'Indexed_Views'
ORDER BY v.name, i.index_id;
SELECT @sql;
-- EXEC sp_executesql @sql;[/CODE]
Now, you can inspect the output using Results to Text in Management Studio, however keep in mind that PRINT is limited to 8K, so if this ends up being a large script, the output will be truncated (so it may not look correct or complete). Unless you need to go to great lengths to validate statements beyond the first 8K, you'll just have to trust that it's all there.
My output is not all that pretty, but it does what I asked:
[CODE] CREATE UNIQUE CLUSTERED INDEX [PK_Sample1] ON [dbo].[Sample1_View]([a])
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON [Indexed_Views];
CREATE UNIQUE CLUSTERED INDEX [PK_Sample2] ON [dbo].[Sample2_View]([a])
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON [Indexed_Views];
CREATE INDEX [IX_Sample2_d__a] ON [dbo].[Sample2_View]([d],[a])
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON [Indexed_Views];
CREATE INDEX [IX_Sample2_b_a__c] ON [dbo].[Sample2_View]([b],[a]) INCLUDE ([c])
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON [Indexed_Views];
CREATE INDEX [IX_Sample2_d__c_a] ON [dbo].[Sample2_View]([d]) INCLUDE ([c],[a])
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON [Indexed_Views];[/CODE]
You'll notice that it created the clustered index for each view first, then the non-clustered indexes in order, and everything is escaped with square brackets to protect you from most poorly-chosen identifiers. You can make certain changes here, for example you can keep certain indexed views where they are (for now, or permanently). You could also adjust the query so that the clustered indexes go on one filegroup and the non-clustered indexes go on another (which is a technique I see used from time to time). So next, you just have to uncomment the final line (`EXEC sp_executesql @sql;`), run it again, and it will move all of the indexed views. (But be sure to test this in a non-production environment first, and the final deployment should be during relative downtime or during a maintenance window - especially if you are not able to use ONLINE = ON.)

If you want to move them back, you just have to change a couple of lines in the last section:
[CODE] WITH (DROP_EXISTING = ON' + @opt + ')
ON [PRIMARY] + ';' -- was ON ' + QUOTENAME(@fg) + ';'
FROM sys.views AS v
INNER JOIN sys.indexes AS i
ON v.[object_id] = i.[object_id]
INNER JOIN sys.filegroups AS f
ON f.data_space_id = i.data_space_id
WHERE f.name = 'Indexed_Views' -- was <> 'Indexed Views'
ORDER BY v.name, i.index_id;[/CODE]
[b] Conclusion[/b]

And there you have it, a single dynamic script that will move all of the indexed views to a specific filegroup. And you can adapt this technique to move indexes on base tables (however you will have to add conditional handling for filtered indexes, which aren't valid on indexed views, and perhaps other differences).

[deals2buy]

[b] Tables and rows listing[/b]



This is my first attempt to create a script which save all the columns of a selected DB in a new table.

[color=#ff0000]WARNING WARNING WARNING If a table with the same name exist, it will be deleted.[/color]

Sometimes in my work, I must do reverse engineering of unknown database structures, trying to migrate their data, and I use this script very often to identify similar data columns.

Disclaimer
Using this script you agree that I am not responsible for any damage of any kind or form type it may be.

[CODE]
/*
This is my first attempt to create a script which show all the columns of a selected DB in a new table.
WARNING WARNING WARNING If a table with the same name exist, it will be overwritten
*/
USE Elma /* Change ME with the database name you want */
GO
/* If the table 'T_STRUTTURA' exist il will be deleted */
IF (ISNULL((SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T_STRUTTURA'), '')) <> ''
BEGIN
DROP TABLE T_STRUTTURA
END/*
Create the table T_STRUTTURA. Note that it is not a temporary table, if you want to swittch it to temporary add # to the name in all the script recurrence
*/
CREATE TABLE T_STRUTTURA
(NOME_TBL VARCHAR (255) CONSTRAINT U_T_STRUTTURA UNIQUE NONCLUSTERED (NOME_TBL, NOME_FLD) NOT NULL,
NOME_FLD VARCHAR (30) NULL,
FLD_TIPO VARCHAR (30) NULL,
FLD_LMAX VARCHAR (30) NULL)
/*
Some variables
*/
DECLARE
@NOME_TABELLA VARCHAR(30), -- table name
@NOME_COLONNA VARCHAR(30), -- column name
@COLONNA_TIPO VARCHAR(30), -- column type
@COLONNA_LMAX VARCHAR(30) -- column lenght/*
I know that the cursors are not the best for someone, but I like them
*/
/*
This is the cursor which contains the table names listing
*/
DECLARE cur_tabella CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE')
ORDER BY TABLE_NAMEOPEN cur_tabella
FETCH NEXT FROM cur_tabella
INTO @NOME_TABELLA
WHILE @@FETCH_STATUS = 0
BEGIN
/* This is the cursor which contains the column names listing */
DECLARE cur_colonna CURSOR
FOR
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @NOME_TABELLA
ORDER BY COLUMN_NAME
/* This start a new table, the other fields are empty so I can filter them to have a list of the table names only */
INSERT INTO T_STRUTTURA SELECT (@NOME_TABELLA + '________________________________________') AS NOME_TBL, ' ' AS NOME_FLD, ' ' AS FLD_TIPO, ' ' AS FLD_LMAX OPEN cur_colonna
FETCH NEXT FROM cur_colonna
INTO @NOME_COLONNA,
@COLONNA_TIPO,
@COLONNA_LMAX
WHILE @@FETCH_STATUS=0
BEGIN
/* The main INSERT for all the columns data */
INSERT INTO T_STRUTTURA SELECT @NOME_TABELLA AS NOME_TBL, @NOME_COLONNA AS NOME_FLD, @COLONNA_TIPO AS FLD_TIPO, @COLONNA_LMAX AS FLD_LMAX
FETCH NEXT FROM cur_colonna
INTO @NOME_COLONNA,
@COLONNA_TIPO,
@COLONNA_LMAX
END
CLOSE cur_colonna
DEALLOCATE cur_colonna
FETCH NEXT FROM cur_tabella
INTO @NOME_TABELLA
END
CLOSE cur_tabella
DEALLOCATE cur_tabella
GO
/* Some samples of how I use this table */
SELECT * FROM T_STRUTTURA WHERE NOME_FLD LIKE '%codciva%'
GO
SELECT * FROM T_STRUTTURA/* When the table is no more necessay */
DROP TABLE T_STRUTTURA
[/CODE]

[deals2buy]

[b] Converting String Values to XML Data[/b]

When converting a string value to XML, you can do so implicitly or explicitly, whether you’re using a literal value or accessing the value through an object configured with a character type. One of the most basic examples of an implicit conversion is to assign a literal string value to an XML object, as I do in Listing 1.

[CODE]
DECLARE @xmlPerson XML;
SET @xmlPerson = '<People><Person>John Doe</Person></People>';
SELECT @xmlPerson;
[/CODE]

[b]Listing 1: Implicitly converting a string value to XML[/b]

First, I declare the @xmlPerson variable with the XML data type. I then assign the <People> element and its contents to the variable. The element in this case is simply a string value that I assign to the variable. SQL Server automatically converts the literal value to XML. When I then use a SELECT statement to retrieve the value from the variable, it’s returned as an XML fragment, as shown in Listing 2.

[CODE]
<People><Person>John Doe</Person></People>
[/CODE]
[b]Listing 2: The XML fragment returned by the T-SQL query[/b]

As you can see, an implicit conversion is fairly straightforward, and it’s just as easy to convert the value in an XML object. For instance, in the example shown in Listing 3, I convert the value assigned to a variable configured with the NVARCHAR data type.

[CODE]
DECLARE @strPerson NVARCHAR(100);
DECLARE @xmlPerson XML;
SET @strPerson = '<People><Person>John Doe</Person></People>';
SET @xmlPerson = @strPerson;
SELECT @xmlPerson;
[/CODE]
[b]Listing 3: Implicitly converting a character type to XML[/b]

First, I declare the @strPerson variable with the NVARCHAR type, and then I declare the @xmlPerson variable with the XML type. Next, I assign the <People> element and its contents (defined as a string literal) to the @strPerson variable. Then I simply assign the @strPerson value to @xmlPerson. Once again, SQL Server automatically converts the data from the NVARCHAR type to the XML type. The SELECT statement returns the same results as the SELECT statement in the previous example. (Refer back to Listing 2.)

We could have just as easily assigned a different character type to the @strPerson variable. For instance, the example shown in Listing 4 assigns the VARCHAR(MAX) data type to the variable.

[CODE]
DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '<People><Person>John Doe</Person></People>';
SET @xmlPerson = @strPerson;
SELECT @xmlPerson;
[/CODE]
[b]Listing 4: Implicitly converting a character type to XML[/b]

Once again, SQL Server automatically converts the character value to XML, and the SELECT statement returns the same results as we saw in the previous examples.

If you plan to port your SQL scripts to another database system, you can’t assume that the system will support implicit conversions in the same way as SQL Server. In such circumstances, you should use the CAST function to explicitly convert your string values to XML. The CAST function conforms to ANSI specifications and consequently is supported by most database systems.

In the example shown in Listing 5, I use the CAST function to convert the @strPerson value to XML before assigning the value to the @xmlPerson variable.


[CODE]
DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '<People><Person>John Doe</Person></People>';
SET @xmlPerson = CAST(@strPerson AS XML);
SELECT @xmlPerson;
[/CODE]
[b]Listing 5: Using the CAST function to explicitly convert a character type[/b]

As you can see, the CAST function takes only two arguments, separated by the AS keyword. The first is the source value, in this case, the @strPerson variable, and the second argument is the target data type—XML. Once again, the SELECT statement returns the same XML element as in the previous examples.

We can easily achieve the same results using the CONVERT function, but we need to structure the arguments differently, as shown in Listing 6.

[CODE]
DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '<People><Person>John Doe</Person></People>';
SET @xmlPerson = CONVERT(XML, @strPerson);
SELECT @xmlPerson;
[/CODE]
[b]Listing 6: Using the CONVERT function to explicitly convert a character type[/b]

In this case, we first specify the target data type (XML) and then the source value (@strPerson), separated by a comma. However, the CONVERT function does not port to other systems; it is specific to T-SQL in SQL Server. The only reason you would use the CONVERT function is to take advantage of additional options available to the function not available to CAST.

Let’s look at a couple examples to better understand how this works. In Listing 7, I start by declaring the two variables and assigning a string value to @strPerson, as I did in the previous examples. But notice that this time I’ve add whitespace and line breaks to the string value.

[CODE]
DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '
<People>
<Person>John Doe</Person>
</People>';
SET @xmlPerson = CONVERT(XML, @strPerson);
SELECT @xmlPerson;
[/CODE]
[b]Listing 7: Trying to preserve whitespace and line breaks when converting string data to XML[/b]

The whitespace and line breaks have no impact on the XML itself. In fact, when SQL Server converts the string to the XML type, it removes the whitespace and line breaks. Consequently, the SELECT statement returns the same results as the previous examples, as shown in Listing 8.

[CODE]
<People><Person>John Doe</Person></People>
[/CODE]
[b]Listing 8: The XML fragment returned without the whitespace and line breaks[/b]

If we want to preserve the whitespace and line breaks, we need to add a third argument to the CONVERT function. The SQL Server documentation refers to this as the [i]style[/i] argument, which is an integer that specifies how to translate the value returned by the expression in the second argument. The styles available are specific to the data type specified in the first argument. For the XML type, we have only a few options available. Two of those are 0 and 1. The 0 option, which is the default, ignores whitespace and line breaks. The 1 option preserves them. Listing 9 shows the CONVERT function when we include 1 as the third argument.

[CODE]
DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '
<People>
<Person>John Doe</Person>
</People>';
SET @xmlPerson = CONVERT(XML, @strPerson, 1);
SELECT @xmlPerson;
[/CODE]
[b]Listing 9: Preserving whitespace and line breaks when converting string data to XML[/b]

As you can see, I’ve simply added a comma and the 1 argument to the CONVERT function. Everything else in the example is the same as the preceding one. However, the SELECT statement now returns the XML with the whitespace and line breaks preserved, as shown in Listing 10.

[CODE]
<People>
<Person>John Doe</Person>
</People>
[/CODE]
[b]Listing 10: The XML fragment returned with the whitespace and line breaks[/b]

Preserving the whitespace is particularly handy when your XML contains more elements and is subsequently more difficult to read. For instance, Listing 11 includes an additional <Person> element in the string value.

[CODE]
DECLARE @strPerson VARCHAR(MAX);
DECLARE @xmlPerson XML;
SET @strPerson = '
<People>
<Person>John Doe</Person>
<Person>Jane Doe</Person>
</People>';
SET @xmlPerson = CONVERT(XML, @strPerson, 1);
SELECT @xmlPerson;
[/CODE]
[b]Listing 11: Preserving whitespace and line breaks when converting string data to XML[/b]

Once again, I’ve used the CONVERT function with the third argument set to 1. As Listing 12 shows, the results have preserved the additional whitespace and line break.

[CODE]
<People>
<Person>John Doe</Person>
<Person>Jane Doe</Person>
</People>
[/CODE]
[b]Listing 12: The XML fragment returned with an additional element[/b]

Keep in mind, however, as handy as the CONVERT function is, in terms of letting you specify how data is converted, the fact that the function cannot be ported to other systems is an important one. If the possibility exists that you will one day need to run your T-SQL scripts against a system other than SQL Server, then you should use the CAST function, and avoid both implicit conversions and the CONVERT function.
[b] Converting XML values to String Data[/b]

At times, you might find it handy to convert XML data to string data. For example, you might decide you don’t need to use the XML data type to store your data and want to switch over to one of the character data types. However, SQL Server does not support implicit conversions from the XML type to a character type. To convert your data in this direction, you must use the CAST or CONVERT function.

If you do try to implicitly convert XML data, you will receive an error. For instance, in Listing 13, I define the same two variables you saw in earlier examples. Only this time, I assign the string value (the <People> element) to the @xmlPerson variable and then assign that variable to the @strPerson variable.

[CODE]
DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = '<People><Person>Jane Doe</Person></People>';
SET @strPerson = @xmlPerson;
SELECT @strPerson;
[/CODE]
[b]Listing 13: Trying to implicitly convert XML data to a character type[/b]

When I try to run these statements, SQL Server returns the error shown in Listing 14. Notice that the error is at Line 4, which is where I try to implicitly convert the XML value to a VARCHAR(MAX) value.

[CODE]
Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type xml to varchar(max) is not allowed. Use the CONVERT function to run this query.
[/CODE]
[b]Listing 14: The error message returned when trying to implicitly convert the XML fragment[/b]

This, of course, is an easy fix. Simply use the CAST function to implicitly convert the data, as shown in Listing 15.

[CODE]
DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = '<People><Person>Jane Doe</Person></People>';
SET @strPerson = CAST(@xmlPerson AS VARCHAR(MAX));
SELECT @strPerson;
[/CODE]

[b]Listing 15: Using the CAST function to explicitly convert XML data[/b]

Notice that I specify the CAST function, with the @xmlPerson variable as the first argument and the VARCHAR(MAX) data type as the second argument. As to be expected, the conversion now works without a hitch, and the SELECT statement returns the expected results, as shown in Listing 16.

[CODE]
<People><Person>Jane Doe</Person></People>
[/CODE]
[b]Listing 16: The XML fragment returned by the query[/b]

I can also use the CONVERT function to achieve the same results. Listing 17 uses the function with the same two arguments used in the previous example for the CAST function.

[CODE]
DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = '<People><Person>Jane Doe</Person></People>';
SET @strPerson = CONVERT(VARCHAR(MAX), @xmlPerson);
SELECT @strPerson;
[/CODE]
[b]Listing 17: Using the CONVERT function to explicitly convert XML data[/b]


As we saw when converting string data to XML data, there might be times when we want to preserve the whitespace and line breaks. The obvious solution is to simply add the third argument to the CONVERT function. So let’s look at what happens when we do. In Listing 18, my string value now includes whitespace and line breaks, and my CONVERT function includes 1 as the third argument.

[CODE]
DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = '
<People>
<Person>Jane Doe</Person>
</People>';
SET @strPerson = CONVERT(VARCHAR(MAX), @xmlPerson, 1);
SELECT @strPerson;
[/CODE]
[b]Listing 18: Trying to preserve whitespace and line breaks when converting XML data[/b]

Unfortunately, this solution will not preserve the whitespace or line breaks, and our SELECT statement again returns the string as a single line, as shown in Listing 19.

[CODE]
<People><Person>Jane Doe</Person></People>
[/CODE]
[b]Listing 19: The XML fragment returned without the whitespace and line breaks[/b]

There’s a reason for this. Earlier in this Level, in Listing 1, you saw how SQL Server implicitly converts a string literal to XML when you assign the value to an XML object. When converting the data, SQL Server removes the whitespace and line breaks. As a result, you must explicitly convert the data when first assigning it to your object, as shown in Listing 20.

[CODE]
DECLARE @xmlPerson XML;
DECLARE @strPerson VARCHAR(MAX);
SET @xmlPerson = CONVERT(XML, '
<People>
<Person>Jane Doe</Person>
</People>', 1);
SET @strPerson = CONVERT(VARCHAR(MAX), @xmlPerson, 1);
SELECT @strPerson;
[/CODE]
[b]Listing 20: Preserving whitespace and line breaks when preserving XML data[/b]

As you can see, I’ve used the CONVERT function when assigning the data to the @xmlPerson variable and then again when assigning that variable value to the @strPerson variable. As Listing 21 shows, the SELECT statement now returns the expected result.

[CODE]
<People>
<Person>Jane Doe</Person>
</People>
[/CODE]
[b]Listing 21: The XML fragment returned with the whitespace and line breaks[/b]

Of course, it makes little sense to explicitly convert a string value to XML and then explicitly convert it back to its original value. But this example helps to demonstrate what happens when converting XML data, so if you get results you don’t expect, you might have some understanding of what’s going on. Also keep in mind that, when viewing an XML document, the application you use might automatically display the XML in a readable format, even though the XML itself doesn’t contain any extra whitespace or line breaks. Yet if you were to view the same XML document as a text file, you might see only one line of text.

[b] Conclusion[/b]

As this Lesson has demonstrated, converting XML data to a string value is a relatively easy process when using the CAST or CONVERT function. And converting a string value to XML is just as easy, if not easier. You can use either one of the two functions, or you can let SQL Server implicitly convert the value. Even if you were to use an XML method to retrieve only a fragment from an XML document, you can still convert the output. For example, you might use the query() method to return an XML element and then convert that element to a string. The key is in understanding how XML data is converted. Once you have that understanding, you’ll be better able to work with the XML documents in your database.