Different ways of finding SQL Version

1. EXEC master..sp_MSgetversion
2. SELECT SERVERPROPERTY('productversion'),SERVERPROPERTY ('productlevel')
3. SELECT @@version
4. EXEC master..xp_msver

The sp_MSgetversion is an Undocumented Stored Proc to find the SQL Version.

Where are Temp tables found???

Temp tables are found in the Tempdb database. we can find the names of the temp tables that are created by executing the below Query

Select * from tempdb..sysobjects where type = 'U'

Status of the Database

The Different Statuses that a database can undergo

1 = autoclose (ALTER DATABASE)
4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)
8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)
16 = torn page detection (ALTER DATABASE)
32 = loading
64 = pre recovery
128 = recovering
256 = not recovered
512 = offline (ALTER DATABASE)
1024 = read only (ALTER DATABASE)
2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)
4096 = single user (ALTER DATABASE)
32768 = emergency mode
4194304 = autoshrink (ALTER DATABASE)
1073741824 = cleanly shutdown

Easy way to copy table structure

Hey friends what you people do you do to copy the structure of your table.I have an simple idea to do that.

1.Go to your Enterprise Manager
2.Go to the database where the required table is located
3.Select the table of your choice
4.Simply press Ctrl+C
5.Then go to Query Analyser and do Ctrl+V
6.Then Press F5
You are done...

Emergency mode

sp_configure 'allow update', 1
Reconfigure with override

update sysdatabases set status=32768 where name=

sp_configure 'allow update', 1
Reconfigure with override

Use this query to take the database to Emergency Mode whenever it is marked as suspect.
When the database is in Emergency mode, you can query the database and export the data into a new database.