sys.tables only searches one database. My quick stored procedure finds a table in every database on the server and returns database, schema and table name.
SQL SERVER – Identifiers As Valid Object Names
Chi-Ho Min found you can skip parts of a four part name with dots, like servername…tablename. Here is how identifiers work as valid object names.
SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS – Part 2
Reader jwwishart shared another way to find database collation in SQL Server 2005: a one line SELECT of collation_name from sys.databases.
SQL SERVER – 2005 – List All Column With Identity Key In Specific Database
Question I received in Email : How to list all the columns in the database which are used as identity key in my database?
SQL SERVER – Introduction to sys.dm_exec_query_optimizer_info
While browsing Books Online I found sys.dm_exec_query_optimizer_info, which returns statistics about the query optimizer to help you tune a workload.
SQL SERVER – 2005 – Find Index Fragmentation Details – Slow Index Performance
A slow index turned out to be heavily fragmented. My quick script uses sys.dm_db_index_physical_stats to show index fragmentation details for every index.
SQL SERVER – 2005 – Find Highest / Most Used Stored Procedure
Which stored procedure runs most often? My small SQL Server 2005 script finds the most used stored procedure, with execution count, worker time and calls per second.