How to DROP,DELETE,SELECT,TRUNCATE all the tables in the Database

Hi All,

      I recently came across the situvation like how to delete all the tables in the database?

     It’s interesting because a single line of statement can do this entire action. I thought to share this with you all.

      The solution is here:

         EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”

 when i start looking further on the same i also idenitfies the following options also,

      To delete all records in all tables in the database use,

     EXEC sp_MSforeachtable @command1 = “DELETE FROM ?”

     To select all records in all tables in the database use,

     EXEC sp_MSforeachtable @command1 = “select * FROM ?”

     To Truncate all records in all tables in the database use,

     EXEC sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”

Note:  The procedure sp_MSforeachtable is hidden in MSSQL

Read Full Post »

How to get the detailed version information in SQL Server?

• We can get the version information in SQL Sever simply by using @@version.
• But it gives the limited information only.
• To get detailed information like FileVersion,ProcessorCount,ProcessorType,etc .,  about your installed SQL Server we can choose xp_MSVer system stored procedure, it will give all the information about the installed SQL Server.

(Please click on image to get clear view.)

Read Full Post »