To logically group tables, proc, views together, all employee related objects in the employee schema etc.,
possibility to assign permissions to a schema makes it worth it from an administration perspective.

create schemas like landing, staging depending on the specific domain
like CDC schema, it will NOT be confusing with real table names
similar to c# namespaces

in 2k, if joe created jobs it will be called as joe.jobs
what if joeleaves company?
then you will have to change the ownership from joeto dbo.jobs
huge administrative effort……so please be mindful

xp_dirtree or dir \b or xp_fileexist

all of them reside in master.dbo.

exec master.dbo.xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL', 1, 0 — get directories
exec master.dbo.xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL', 0, 1 — get files

It does only as its name implies … tests for the existence of a FILE.
It returns 0 if you try to use it to test for a folder

declare @Path varchar(128)

declare @Path varchar(128) ,@FileName varchar(128)
select @Path = 'C:\temp\jon'
declare @cmd varchar(1000)
create table #a(s varchar(1000))
select @cmd = 'dir /B ' + @Path
insert #a exec master..xp_cmdshell @cmd

set assigns only one variable at a time
if more than one match found, it errors out
set is ANSI standard
set assigns null if not match

select multiple variables can be assigned
if more than one found it picks the last value – incorrectly – need to check the logic
select is NON-ANSI standard
when no match it doesnt RESET the value to null but it retains its original value

Database as untrustworthy, meaning that objects in the database cannot access objects in other databases in an impersonated context.

set @sql = 'ALTER DATABASE ' +
QUOTENAME(@db_name) + ' SET
TRUSTWORTHY OFF'
execute( @sql )