What Was I Thinking?

Follies & Foils of .NET Development
posts - 95 , comments - 352 , trackbacks - 0


SQL, T-SQL, and SQL Server Related
Get a list of all columns that participate in Foreign Key relationships
The following query returns all the column information for columns in the specified table that participate in a FK relationship. You can modify the query to return PK information by changing the constriant_type filter. select * from information_schema.columns where table_name = <TableName> AND table_schema=<Schema>and column_name not in (SELECT Col.Column_Name from INFORMATION_SCHEMA.TABLE_CO... Tab, INFORMATION_SCHEMA.CONSTRAI... Col WHERE Col.Constraint_Name = Tab.Constraint_Name ......

Posted On Thursday, June 25, 2015 6:20 PM | Comments (1) | Filed Under [ SQL ]

Troubleshooting Blocked Transaction in SQL Server
While troubleshooting a blocked transaction issue recently, I found this code online. My apologies in not citing its source, but its lost in my browse history some where. While the transaction is executing and blocked, open a connection to the database containing the transaction and run the following to return both the SQL statement blocked (the Victim), as well as the statement that’s causing the block (the Culprit) -- prepare a table so that we can filter out sp_who2 results DECLARE @who TABLE(BlockedId ......

Posted On Friday, December 7, 2012 4:45 PM | Comments (0) | Filed Under [ SQL ]

Convert VARCHAR() columns to NVARCHAR()
We recently underwent an upgrade that required us to change our database columns from varchar to NVarchar, to support unicode characters. Digging through the internet, I found a base script which I modified to handle reserved word table names, and maintain the NULL/NotNull constraint of the columns. I Ran this script use NWOperationalContent – Your Catalog Name here GO SELECT 'ALTER TABLE ' + isnull(schema_name(syo.id), 'dbo') + '.[' + syo.name +'] ' + ' ALTER COLUMN [' + syc.name + '] NVARCHAR(' ......

Posted On Wednesday, September 26, 2012 6:46 PM | Comments (1) | Filed Under [ SQL ]

Query SQL Server’s schema to find all tables containing a column named ‘x’

The following query will find all tables in my catalog with a column name like ‘city’

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
WHERE c.name LIKE '%City%'
ORDER BY schema_name, table_name;

Posted On Thursday, April 26, 2012 10:59 AM | Comments (2) | Filed Under [ SQL ]

How to get a Product() (multiplication result) rather than a SUM() (addition result) using T-Sql
Get a summary aggregation of rows in T-Sql is easy thanks to the Sum operator: Select Sum(Qty) From Table Why is there no Product() aggregation operation for T-Sql? Sometimes I want the values multiplied, not added. Luckily, some one who is much smarter in math than I, observed: log(A * B) = log(A) + log(B) So, summing the log, and converting back to its exponential value will yield its product. Select CAST(EXP(SUM(LOG(Qty))) as int) as ExtendedQTY Happy Calculating! UPDATE: The above expression ......

Posted On Wednesday, February 29, 2012 2:32 PM | Comments (1) | Filed Under [ SQL ]

Enabling Sql Server Broker Service Hangs
If your connection hangs while attempting to start sql server broker service, its likely caused by the system trying to gain exclusive access to your database. Some people recommend stopping and restarting the sql server instance. I find that a little heavy-handed, like swatting a fly with a sledge hammer. Instead switch the database into single user mode, enable the broker service, and restore the database to multi-user mode. 1) Set the database to single user mode: ALTER DATABASE [DBNAME] SET SINGLE_USER ......

Posted On Saturday, January 28, 2012 3:53 PM | Comments (1) | Filed Under [ SQL ]

Migrating SQL Server Broker Service (SSBS) Objects
I recently migrated my dev environment to a new pc. I moved over all the databases and reattached them. Unfortunately, I soon discovered my SSBS services weren’t working. I was sending messages to my service, but nothing was showing up in the queue. I added logging and monitoring to the stored procs that act as an entry point to the services, and the stored procs I use for activation. Still, nothing. When I ran the Broker Server Diagnostic tool (ssbdiagnose) (available in {Program files}\Microsoft ......

Posted On Friday, November 5, 2010 4:58 PM | Comments (2) | Filed Under [ SQL ]

QuickTip: Working with T-SQL Identities
I recently had to write a data migration script from one SQL Server DB to another one. I could have used SSIS, but it would have required a learning curve that our timeframe didn't permit. A few hours later I had a functioning script that moved over most of the useful data (2 days later I discovered some more data that had to be migrated, but that's a separate issue). In developing the migration script I had to deal with a series of key pool tables, each with their own identity columns for key generation. ......

Posted On Saturday, March 8, 2008 8:48 PM | Comments (0) | Filed Under [ SQL ]

Powered by: