Archive for January, 2011

  |  
January 25, 2011

Re-index All Tables in your SQL Database: Short & Dirty Script

I am not advocating that you use this in a production system, but if you’re looking to re-index all tables in your database (say on development or test), here is a short script for it:

exec sp_msforeachtable 'dbcc dbreindex(''?'', '''', 80)'

Note, the last parameter controls the fill factor, set it to 0 to re-use the last known fill factor or read more about it in Books Online. With sp_msforeachtable you’re looping through all tables in the database and executing the provided script. As you can see above you use a question mark (?) to substitute the table name. This is a decent alternative to manual looping through INFORMATION_SCHEMA.TABLES which is another way to get a list of all tables in your database.

If you were just looking to obtain a dbcc script you can run this command and it will generate the equivalent script that is run by the first command I showed:

select 
	'dbcc dbreindex(''' + TABLE_NAME + ''', '''', 80)' 
from 
	INFORMATION_SCHEMA.TABLES 
where 
	TABLE_TYPE = 'BASE TABLE'
Advertisement

Posted in T-SQL | Leave a Comment »

Tags: ,
January 3, 2011

T-SQL: Parsing data type xml

If you only occasionally dabble in XML and XPath utilizing the XML data type in SQL Server can be a bit daunting. I found it very useful in passing parameters to stored procedures containing object graphs. The same can be accomplished the table parameter if you only have a list of one type of objects, but if you need to pass an object graph of parent/child objects of different types, using XML will be your best bet.

Below I demonstrate how one could represent a list of Contact objects in an XML document and subsequently access the data via a select statement. the @Contacts parameter could be used as a parameter to a stored procedure if you’re running SQL 2005 or higher. Use your imagination for the rest.


declare @Contacts xml

set @Contacts = 
    '<root>
    <Contact>
        <ContactID>1</ContactID>
        <Email>contact1@contact1.com</Email>
    </Contact>
    <Contact>
        <ContactID>2</ContactID>
        <Email>contact2@contact2.com</Email>
    </Contact>
    <Contact>
        <ContactID>3</ContactID>
        <Email>contact3@contact3.com</Email>
    </Contact>
    </root>'


select
    ContactID = Contact.value('ContactID[1]', 'int'),
    Email = Contact.value('Email[1]', 'varchar(255)')
from
    @Contacts.nodes('root/Contact') as Contacts([Contact])

Posted in T-SQL | Leave a Comment »

Tags: , ,
  |