The following SQL will list all indexes in within the database that you run the sql script.  I was looking for the option within Visual Studio - Data Dude add-on that displayed index differences between two databases.  I eventually discovered the line by line index comparison by exploding the Table-Indexes however I was unable to create the newly added script index in the 2nd database.  Is this possible? In lieu of the shortcomings within Data-Dude I used the following script to manually compare and script the indexes in both databases.DECLARE GetTables CURSOR READ_ONLY FOR SELECT TABLE_NAME FROM...

I had a need to compare two SQL Server table for differences between them.  I started using .NET dataset features (merge, acceptchanges, getchanges) as follows:         Dim data1 DataSet = GetData1()          Dim data2 DataSet = GetData2()            Dim ds As New DataSet         ds.Merge(data1)         ds.AcceptChanges()         ds.Merge(data2)         ds.GetChanges(DataRowState.Modified) There are a few gotcha's with the above code.  The primary problem was that both tables must have primary keys defined.  I figured ok, I could create primary keys through code for the related DataTables however I soon realized that there were duplicate rows within the tables....

I was returning to an old database and I wanted to find all instances of a particular column name.  The following SQL is a very fast way to output the object name (with additional data) for locating the item of interest.  Of course you can make more elaborate by filtering query. SELECT sc.[name] AS column_name, so.[name] , * FROM syscolumns sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.[name] LIKE '%TestColumnName%'

I have been chasing an issue for quite a while where the query would timeout when executed from an ASP.NET interface.  If I ran that exact same query through Query Analyzer the results would be returned in less than 2 seconds.  I have for a while been struggling with why it is different between those two interfaces.  I thought about connection pooling issues, command time outs and connection timeouts and was focused on that for a while.  Even after extending those values from the default the query would still time out.  It was often I would see an...

This is a free (please donate) SQL Server Management Studio add-on that is small, easy to install and is packed with some great features. Query Execution History – every sql statement that you run is automatically logged and available at a later time Search Database Data – search through all non-binary columns in every table of the database for a search term. Uppercase/Lowercase Keywords Run one Script on multiple databases Copy execution plan bitmaps to clipboard Generate Insert statements for...

“Cannot resolve collation conflict for equal to operation” – huh? When joining fields in different collated databases you can see the above error message.  In my particular case I had two databases with different collations and I was doing a join across the databases on the fields. The resolution was to add the keywords “COLLATE DATABASE_DEFAULT” near the equal ‘=’ signs. For example: SELECT p1.BEEF_1 FROM dbo.PHEN p1 INNER JOIN  Database2.dbo.PHEN p2...

Before you rush to download SQL 2008 you may want to read the following KB article which warns that Visual Studio 2008 SP1 ‘may be required’ for SQL Server 2008 installations KB956139 (found here) ”Because certain SQL Server 2008 features install components that are also part of the release version of Visual Studio 2008 SP1, SQL Server 2008 requires Visual Studio 2008 with SP1. If Visual Studio 2008 without a service pack is installed instead, it may not work correctly after you install SQL Server 2008.“ Currently Visual...

Credit goes to the Cincinnati SQL Server User’s Group/Phil Japikse for the following tip.  Two things of interest in this post.  Ability to create Sql Templates within SQL Server Management Studio and SQL to find objects named like ‘%here%’ across all objects in a database. USE <databasename,string,@p1> GO SELECT OBJECT_NAME(OBJECT_ID) FROM sys.sql_modules WHERE definition like '%<string_to_find,string,@p2>%'  Now that you have the SQL it is possible to create a SQL Template (View – SQL Templates) whereby you can simply select and push Ctrl-Shift-M for variable substitution.  Within the Template Window create a new folder to store your custom templates, create a new template,...