Here a cursor free version:
DECLARE @TableName SYSNAME = 'MyTable',@Schema SYSNAME = 'dbo', @sql NVARCHAR(MAX) = '', @max-2 INT
,@UnionALL NVARCHAR(11)= '';

SELECT @sql += @UnionALL +...

Assuming we use DBCC SQLPERF with the parameter LOGSPACE the explanation is wrong.
"Check the amount of space available in a transaction log file."

BOL says the opposite "amount...

About the "tempdb", here says that it is configured at the smallest possible size.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-with-minimal-configuration

Correct and incomplete answer: "This lets me know if the Order table is in the buffer pool", it always returns false.
Ambiguous answer: "This tells me nothing as tables cannot...

The best method is the following:
SELECT COUNT(*) FROM sys.triggers
WHERE parent_id = OBJECT_ID('TriggerTest')


Uncomment the query you want to try:
WITH Fullname
AS
(
SELECT * FROM (VALUES
('Erin','Keri','Moody','Sr.')
,('Megan','Laura','Morales','B.S.')
,('George','Lena',NULL,'')
,(NULL,'Ryan','Lucas','M.D.')
,('Sheryl','Marianne','Morton','IV')
) AS V([firstname],[middlename],[lastname],[suffix])
)
--SELECT COALESCE
--        (
--        firstname,...