Find a Column in Your Database by Name

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%isTextbook%'
ORDER BY schema_name, table_name;

Props to SQL Authority.

Convert HTML Stored in SQL Server as varbinary to Text

I have a wonderfully exciting legacy database I'm handling and it contains a varbinary(MAX) field with HTML. For debugging purposes, every once in awhile I need to dig in and review what's stored there. Here's how I do that:

SELECT CONVERT(VARCHAR(MAX), mainText) AS htmlString FROM entry WHERE entryid = 1380915

Find Duplicate IDs in a Table with SQL

This is a simple way to find all the rows that have duplicate IDs / values in a table.

SELECT entryid, Count(entryid) AS ecount 
FROM filelocation 
GROUP BY entryid
HAVING (Count(entryid) > 1) -- This runs after the aggregate count() function

See Unencrypted DTSRun Commands

If you come across encrypted DTSRun commands, here's a way to decrypt them.

Drop down to the command prompt and paste in the full DTSRun string:

C:\>DTSRun /~ZThisWouldBeYourBigUglyDTSRunCommandString /!X /!C

Everything after C:\> is directly from the Job's Command text box. You have to add /!X and /!C to the end. Hit Enter to run it and the results then end up in your "paste" buffer, so you can just Ctrl+V into Notepad or wherever.

This seems to fail if the DTS is connecting to a server not setup on your machine. You may see something like the error below. Just add the remote machine to Enterprise Manager.

Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Thanks to the guys over at RDA Blogs for posting about this.

T-SQL SPROC to Re-Create a Full Text Catalog

I cannot claim any ownership over this code. It's a colleague's, but it's so handy that I have to document it somewhere.

This will create an SPROC to re-create a full text catalog, which of course can then be tied to a Job.

CREATE PROCEDURE p_RecreateFT_Articles
 
AS 
 
ALTER TABLE [dbo].[Articles] WITH NOCHECK ADD 
 CONSTRAINT [PK_Articles] PRIMARY KEY  CLUSTERED 
 (
  [Article_ID]
 )  ON [PRIMARY]
 
IF EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE NAME = 'FT_Articles')
EXEC sp_fulltext_catalog 'FT_Articles', 'drop'

-- Create the catalog if it doesn't already exist.
IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE NAME = 'FT_Articles')
EXEC sp_fulltext_catalog 'FT_Articles', 'create'

-- Add the full text index to the table 
EXEC sp_fulltext_table '[dbo].[Articles]', 'create', 'FT_Articles', 'PK_Articles'

-- Add the columns to the full text index 
EXEC sp_fulltext_column '[dbo].[Articles]', 'ArticleTitle', 'add' 
EXEC sp_fulltext_column '[dbo].[Articles]', 'ArticleBody', 'add'

-- Activate the index
EXEC sp_fulltext_table '[dbo].[Articles]', 'activate' 

-- Start population
EXEC sp_fulltext_catalog   'FT_Articles', 'start_full'

GO

T-SQL INSERT From One Table Into Another

This is just a simple example of doing an INSERT INTO one table from another, with a hard-coded value thrown into the mix.

INSERT INTO ProductSubjects  (ID, ProductID, SubjectID)
SELECT NEWID(), '52EB01B6-B768-4F9B-8F60-1A695A13D945', Subjects.ID 
FROM Subjects

T-SQL SELECT Using CASE and String Concatenation

Here I'm selecting the ID field followed by a concatenation of 3 different subject fields, depending on whether or not the fields are NULL.

SELECT
  ID, 
  CASE 
  WHEN Subject2 IS NULL THEN 
    Subject1
  WHEN Subject2 IS NOT NULL AND Subject3 IS NULL THEN 
    Subject1 +  ' : ' + Subject2
  ELSE Subject1 + ' : '  + Subject2 + ' : '  + Subject3
  END AS TheSubject
FROM Subjects
ORDER BY TheSubject

Logins Fail after SQL Server Restore

After doing a SQL Server db restore, logins can be a problem. This script will re-sync the passwords.

EXEC sp_change_users_login 'Auto_Fix','UserOne', null, 'pwd1'
EXEC sp_change_users_login 'Auto_Fix','UserTwo', null, 'pwd2'
EXEC sp_change_users_login 'Auto_Fix','UserThree', null, 'pwd3'

To find these users.

EXEC sp_change_users_login 'Report', null, null, null

Update a Table Based on Values from a Separate Table

Here's a script to update a table based on values from a separate table, where the two tables can be joined by an ID field.

UPDATE destinationtable
SET destinationtable.path = sourcetable.path
FROM sourcetable
WHERE destinationtable.id = sourcetable.id

Updating the Noise Word File in SQL Server 2000

I was trying to update two noise word files in SQL Server 2000 today, but there was a file lock holding on tight. The files were:

• C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config\noise.dat
• C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config\noise.eng

I stopped all the SQL Server services and the Indexing Service, but the files were still locked. After a little Googling, I found Unlocker, a very handy tool. This told me that the Microsoft Search service had the lock. I used Unlocker to remove the locks, made my edits, and restared all the services (including Microsoft Search for good measure).

SELECT Using a Full Text Catalog in SQL Server

This is a pretty simple example. There's a heck of a lot more that you can do.

SELECT Distinct(i.imageFile), i.caption, i.fileID, i.fragmentID, i.path, c.title 
FROM metadata_image AS m JOIN image AS i ON m.image_id=i.id 
LEFT JOIN fragments AS c ON i.fragmentID=c.cid 
WHERE Contains(i.*, '"some" or "word"') OR 
  Contains(c.*, '"some" or "word"') OR 
  Contains(m.*, '"some" or "word"') 
ORDER BY c.title, i.caption

ALTER Full Text Catalogs in SQL Server 2005 for Diacritic Sensitivity

Pretty simple:

USE AdventureWorks;
GO
ALTER FULLTEXT CATALOG ftCatalog
REBUILD WITH ACCENT_SENSITIVITY=OFF;
GO

SELECT Duplicate Records with Matching IDs

So painfully simple, but I can never remember this when I need it, so here it is!

SELECT l.fileid, l.title, a.title
FROM pub_books AS l
JOIN books AS a
ON l.fileid = a.fileid