Talking about Some SQL Queries

How to Run commands for all tables in current dB:

EXEC sp_MSforeachtable
@command1 = ‘DELETE FROM ? WHERE your_condition’

The ‘?’ will be replaced by the table name

——————————————————————————–

How to find Size of all user tables with the number of rows:

EXEC sp_MSforeachtable
@command1=’sp_spaceuse d “?”‘

——————————————————————————–

How to find Number of rows in a table and the space the table and
index use (in KB):

EXEC sp_MStablespace table_name

——————————————————————————–

How to display Size of the datafile and log separately:

EXEC sp_helpdb database_name

——————————————————————————–

How to List of all hard drives and the amount of free space in MB
for each drive:

EXEC master..xp_fixeddrives

——————————————————————————–

How to return all tables, which contain a specified column:

SELECT a.name ‘Table’
FROM sysobjects a
JOIN syscolumns b
ON a.id = b.id
WHERE a.type = ‘U’ AND
b.name = +
‘your_column’

——————————————————————————–

How to Delete a file from the server in SQL:

xp_cmdshell “cmd /c del <filename_with_path>

——————————————————————————–

How to Control State Options of the Database:

ALTER DATABASE db_name
SET SINGLE_USER
WITH ROLLBACK AFTER x SECONDS

or

ALTER DATABASE db_name
SET RESTRICTED_USER
(or MULTI_USER)
WITH ROLLBACK IMMEDIATE

(Roll back transactions after the specified number of seconds or
immediately.)