General purpose of it is to return object ID based on given name but we use it to check if the object already exists or not before we attempt to drop it or create it. If OBJECT_ID will return NULL for an object name that means it does not exists and vice versa.
N'some string here' - this N next to the string enclosed in single quotes mean that the string is NVARCHAR type if N wouldn't be there it would be VARCHAR
IF OBJECT_ID('TestTable', 'U') IS NOT NULL
DROP TABLE TestTable
SELECT N'test string' as ntest, 'test string' test INTO TestTable
SELECT OBJECT_ID('TestTable') ObjectID, OBJECT_NAME(OBJECT_ID('TestTable')) ObjectName
EXEC sp_help 'TestTable'
IF OBJECT_ID(N'TestTable', N'U') IS NOT NULL
DROP TABLE TestTable