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 )

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@Handle)

SELECT * FROM sys.sysprocesses s CROSS APPLY fn_get_sql(sql_handle)