I sometimes come across very strange requirements and often I do not receive a proper explanation of the same. Validating Unique Column Name
SQL SERVER – INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1
Why does INFORMATION_SCHEMA.COLUMNS show -1 as the character maximum length? A reader asked me this simple question, and I explain what the negative value means.
SQL SERVER – Get Schema Name from Object ID using OBJECT_SCHEMA_NAME
Many people join sys.tables and sys.schemas to get a schema name from an object ID. The OBJECT_SCHEMA_NAME function does it in one simple call.
SQL SERVER – Resource Database ID – 32767
Why does DB_NAME(32767) return NULL? Resource Database ID 32767 belongs to the hidden Resource Database, which you will not find in sys.databases.
SQL SERVER – Denali – ObjectID in Negative – Local TempTable has Negative ObjectID
On Denali I saw many negative ObjectID values in sys.all_objects. Here is how I found out that each local temp table gets a negative ObjectID.
Finding Every Object That Uses a Column
Find an object that uses a column with dependency metadata and module text search, then check dynamic SQL and application code.
SQL SERVER – System Stored Procedure sys.sp_tables
Most people run SELECT * FROM sys.tables to list tables. If you do not need all those details, sys.sp_tables returns tables and views with schema names.