Little queries that save you time

Which tables in the database use an identity field?

SELECT [name]
FROM sys.objects
WHERE ObjectProperty(object_id,'TableHasIdentity')=1

Search for a text inside triggers and stored procedures. To be used, for instance, if you need to know where a particular table or stored procedure or function is used (for removal).

SELECT sysobjects.name, syscomments.text
FROM syscomments INNER JOIN sysobjects ON syscomments.id = sysobjects.id
WHERE (sysobjects.xtype = 'P' OR sysobjects.xtype = 'TR')
AND syscomments.text LIKE '%SEARCHEDTEXT%'
ORDER BY sysobjects.name

If you are using SQL Server 2005 you can also make use of sys.sql_modules:

SELECT sys.objects.name, sys.sql_modules.definition
FROM sys.sql_modules INNER JOIN sys.objects on sys.sql_modules.object_id = sys.objects.object_id
WHERE sys.sql_modules.definition LIKE '%SEARCHEDTEXT%'

How to copy SQL Server diagrams from one server ORIGIN, to another server DESTINATION? Provided both databases have already the same tables and schemas, you can copy the definitions of the diagrams in ORIGIN creating a linked server to ORIGIN in DESTINATION and then running this simple query at DESTINATION (both databases have the same name mydatabase):

use mydatabase
set identity_insert dbo.sysdiagrams on
insert dbo.sysdiagrams (name, principal_id, diagram_id, version, definition)
  select name, principal_id, diagram_id, version, definition
  from ORIGIN.mydatabase.dbo.sysdiagrams
set identity_insert dbo.sysdiagrams off

How do I find all the tables referenced by Stored Procedures or Functions?

SELECT so.name AS FuncProcName, t.TABLE_NAME AS TableName, sc.text AS Definition
  FROM syscomments sc
    INNER JOIN sysobjects so ON sc.id = so.id
    INNER JOIN INFORMATION_SCHEMA.Tables t ON sc.text LIKE '%'+t.TABLE_NAME+'%'
  WHERE (so.xtype='FN' OR so.xtype='P') AND so.category=0

How do I find the names of all tables containing a field called like criteria?

SELECT sys.objects.name, sys.columns.name
FROM sys.columns INNER JOIN sys.objects ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.name LIKE '%criteria%' AND sys.objects.type = 'U'
ORDER BY sys.objects.name