Truncate with where clause

No, Truncate can't be used with a WHERE clause. Truncate simply deallocates all the pages belonging to a table (or a partition) and its indexes.

From BOL:

-- Syntax for SQL Server and Azure SQL Database  

TRUNCATE TABLE   
    [ { database_name .[ schema_name ] . | schema_name . } ]  
    table_name  
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }   
    [ , ...n ] ) ) ]  
[ ; ]  

If you're looking for a more efficient way to delete data, I'd start here.

There are three delete methods in sql server: Truncate , Delete , Drop

DROP, TRUNCATE are DDL commands (DROP is used to remove objects like tables, columns, constraints,...but not rows)

DELETE is a DML commands.

"TRUNCATE Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources..." Read more

You have to create command using a dynamic sql and execute it: (something like this query)

DECLARE @strquery as NVARCHAR(MAX)

SET @strquery = ''
SELECT 'Delete T2 from [' + Table_name + '] As T2
Inner join  DimEmployee as T1 
On T1.[EmployeeKey] = T2.[ProductKey] 
Where    T1.[FirstName] like ''kevin%'';'
 From information_schema.tables
 WHERE table_name <> 'DimEmployee'


EXEC(@strquery)

Helpful links

• Drop vs Truncate vs Delete (oracle) infos are similar to sql server
• Truncate vs Delete (sql server)
• Delete using inner join with sql server