To find SQL Server Version

This article describes how to identify your current SQL Server version number and the corresponding product with service pack information as well. It also describes how to identify the specific edition of SQL Server you are using.

Type the following in the Management studio

Before running press CTRL T to change results to text mode from grid view so you can view results in the screen without truncated

Select @@version

The following will work for Sql Server 2000, 2005 and 2008 also

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

To Search Table Names in a database [Microsoft SQL Server Only]

Sometimes we may need to search table names in one are more databases. You may use the following query to search table names

Read more here:   Search Table Names 

To Query Table structure [Microsoft SQL Server Only]

Whenever i need the table structure earlier i used the following Query for getting the table structure. Now in Sql Server 2008 i can use the ALT+F1 to get the sql structure of table. 

But Sometimes we need to query the structure of a table(For e.g to use in your website ASP.Net to show ). You may use the following query to find the structure of the database.

Source Code

code]Select
      Sc.Name
From
      SysObjects SO Inner Join
      SysColumns SC On SO.Id= SC.Id
where
      SO.Name = 'Sysobjects'
 
'Aternatively you can use sp_help to find the structure of the table like following
sp_Help  Sysobjects[/code]

To List All Database Names in the server

Sometimes we need to list all the databases in a server. The following query will be useful to list all the databases in the server.Sys.Databases contains all the database names. For filtering simply use the where clause with the required wild cards and boolean

To List All Table Names in the server

Query across two different databases

I ran few times into this kind of situations, when I need to read two different databases and present it in single data table. It is always better to handle this in the db level. If not it is going to be a heavy looping at the server side.

So the following few samples are what I have used to fetch and combine data from different sources.

If you need data from two different databases, then

Union

SELECT * FROM DB1.dbo.Table1

Union

SELECT * FROM DB2.dbo.Table1

Inner Join

SELECT

            *

FROM

            DB1.dbo.Table1 one inner join

            DB2.dbo.Table1 Two on One.ColumnOne = Two.ColumnOne

If two databases are in different machines

SELE ...