Find columns with a particular property

Here's a query I found handy when I needed to retrieve a list of columns from any table in a database that is a ROWGUID column.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMNPROPERTY
(
OBJECT_ID(QUOTENAME(table_schema) + '.' +
QUOTENAME(table_name)) -- table ID
,column_name
,'IsRowGuidCol'
) = 1
ORDER BY table_name, ordinal_position

You can also replace 'IsRowGuidCol' with any other property listed here.

Clean up orphaned identity range constraints

Consider the following scenario: You wanted to move a SQL Server database from one server to another, so you detached it from the original server and attached it at the destination. However, the database was replicated using merge replication and you forgot to delete the database's publications before detaching it. Now, after attaching the database, you find that there are leftover orphaned identity range constraints on the previously replicated tables. I was faced with this problem, so I constructed the following script after some searching on the internet.

DECLARE @constraintname SYSNAME
DECLARE @tablename SYSNAME
DECLARE c1 CURSOR FOR
SELECT SysObjects.[Name] AS [Contraint Name] , Tab.[Name] AS [Table Name]
FROM SysObjects
INNER JOIN (SELECT [Name],[ID] FROM SysObjects WHERE XType = 'U') AS Tab ON Tab.[ID] = Sysobjects.[Parent_Obj]
INNER JOIN sysconstraints ON sysconstraints.Constid = Sysobjects.[ID]
WHERE SysObjects.[Name] LIKE 'repl_identity_range%'
ORDER BY Tab.[Name]

OPEN c1
FETCH NEXT FROM c1 INTO @constraintname, @tablename
WHILE (@@fetch_status <> -1)
BEGIN
EXEC ('ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constraintname)
FETCH NEXT FROM c1 INTO @constraintname, @tablename
END

DEALLOCATE c1
GO

Find text in stored procedures or views

This is a handy query that I found that searches through your SQL Server database stored procedures or views for occurrences of the specified text.

For example, to search for a stored procedure with the text "foo", use the following:

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE lower([text]) LIKE '%foo%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

To search through views, replace 'IsProcedure' with 'IsView'.