Surprisingly, SQL Server doesn’t automatically create indexes just because you have a foreign key. So at the end of a development project, I just used the script below to generate the SQL needed. Copy Text Copied Use a different Browser select 'create index indx_'+t.name +'_' + c.name + ' on ' + t.name + '(' […]
Generating Indexes for Foreign Keys Read More »
On one of our projects, we have a requirement that we have to change our passwords every 90 days. So I created a page where I can just click a button and it will generate a password, change the password on the database for the user in the connection string, and then change the password
Change Connection String Password Programmatically Read More »
I had a large number of XML files that each contained hourly prices for a lot of different locations. I needed to get all of the prices for a single location. I did this by loading all of the files into a table and then querying the table. First, I needed to change the rights
Uploading XML Files to SQL Server Read More »
In the past we would use a Varchar to pass a comma delimited list of IDs to a stored proc. We would then build a big varchar string of the full SQL and run the dynamic SQL. Starting with SQL Server 2005 you can pass the stored proc XML and do your select without using
Passing IDs to Stored Procs Using XML Read More »
I use YetAnotherForum (YAF) for my Forums here. Like others, I get hundreds of spam users created each month. I wrote the following SQL to delete any user that hasn’t visited in the last day, never posted or emailed a message, isn’t the last user on a forum, and isn’t in the Active list.
Delete Spam Users from YetAnotherForum Read More »
Real quick I wanted to know what tables and stored procs were in my ReleaseManagerTest database and not in my ReleaseManager database. So here is the SQL for that. select min(DB) as DB, name, type from ( select ‘ReleaseManagerTest’ as DB, name, type from ReleaseManagerTest.sys.objects union all select ‘ReleaseManager’ as DB, name, type
Quickly Finding Missing Objects in Two SQL Server 2005 Databases Read More »
I kept writing the same SQL over and over while developing to find the names of stored procs or to see what objects I had recently added. So I created a quick stored proc that I now have in my toolbox. Copy Text Copied Use a different Browser create proc obj(@name varchar(50)=null, @type char(1)=null) as
Finding Objects in SQL Server During Development Read More »
We were changing our linked server and needed to test every page in our website that used that linked server. None of the pages used it directly, but they did call stored procs that would use it or stored procs that would in turn, call other stored procs that use it. I need to find
Knowing What to Test When Changing a SQL Server Object Read More »
Part of my pattern during development is to: 1. Create a new table 2. Create the stored procs to get data and save data This second step is tedious and lends itself to automation. So I created the stored proc createSPFromTable that takes the name of a table and creates a simple save store proc.
Generate Save Stored Proc from Table Read More »
Part of my pattern during development is to: 1. Create a new table 2. Create the stored procs to get data and save data 3. Create the data access layer methods that call the stored procs. This is a little tedious, so I created a SQL stored proc called createDALFromSP to generate a data access
Generate Data Access Layer Methods From Stored Procs Read More »
