How To Get SQL Server Version

Option 1

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

and get the details on the link: http://support.microsoft.com/kb/321185

Option 2

EXEC master..xp_msver

Option 3

SELECT @@version

Option 4

-- Option 4
DECLARE @ver NVARCHAR(128) = CAST(serverproperty('ProductVersion') AS NVARCHAR)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

SET @ver =
                CASE
                        WHEN @ver = '7' THEN 'SQL Server 7'
                        WHEN @ver = '8' THEN 'SQL Server 2000'
                        WHEN @ver = '9' THEN 'SQL Server 2005'
                        WHEN @ver = '10' THEN 'SQL Server 2008/2008 R2'
                        WHEN @ver = '11' THEN 'SQL Server 2012'
                        WHEN @ver = '12' THEN 'SQL Server 2014'
                        ELSE 'Unsupported SQL Server Version'
                END
SELECT @ver

Truncate All Tables in Database

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--some of the tables have identity columns we may want to reseed them
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Data Type Conversion

Implicit and Explicit Conversion

---

Data types can be converted either implicitly or explicitly.

Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

GETDATE() implicitly converts to date style 0. SYSDATETIME() implicitly converts to date style 21.
Explicit conversions use the CAST or CONVERT functions.

The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another. For example, the following CAST function converts the numeric value of $157.27 into a character string of '157.27':

CAST ( $157.27 AS VARCHAR(10) )

Use CAST instead of CONVERT if you want Transact-SQL program code to comply with ISO. Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.
The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

Find Particular Database and All tables Sizes

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t

Hold the Lock on Table and Execute Query First

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRAN T1

 DELETE FROM Temp WITH(HOLDLOCK, ROWLOCK);

COMMIT TRAN T1

Modifying SQL Server Synonyms (Can't Alter Synonyms)

DECLARE
    @DropSynonym NVARCHAR(4000),
    @CreateSynonym NVARCHAR(4000),
    @Permissions NVARCHAR(4000)

SELECT
    @DropSynonym = '',
    @CreateSynonym = '',
    @Permissions = ''

SELECT
    @DropSynonym = @DropSynonym + 'DROP SYNONYM '
    + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(NAME) + ';
',
    @CreateSynonym = @CreateSynonym + 'CREATE SYNONYM '
    + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) + '.' + QUOTENAME(NAME) + ' FOR '
    + REPLACE(base_object_name, '[OldDB]', '[NewDB]') + ';
'
FROM
    sys.synonyms ;
WITH    PermQuery
          AS (SELECT
                CASE WHEN perm.state <> 'W' THEN perm.state_desc
                     ELSE 'GRANT'
                END COLLATE database_default AS PermissionState,
                perm.permission_name COLLATE database_default AS Permission,
                SCHEMA_NAME(obj.schema_id) AS SchemaName,
                obj.name AS ObjectName,
                CASE WHEN cl.column_id IS NULL THEN SPACE(0)
                     ELSE '(' + QUOTENAME(cl.name) + ')'
                END AS ColumnName,
                CASE WHEN perm.state <> 'W' THEN 'N'
                     ELSE 'Y'
                END AS WithOption,
                usr.name AS UserName
              FROM
                sys.synonyms AS s
                INNER JOIN sys.all_objects AS obj
                    ON s.object_id = obj.object_id
                INNER JOIN sys.database_permissions AS perm
                    ON perm.major_id = obj.[object_id]
                INNER JOIN sys.database_principals AS usr
                    ON perm.grantee_principal_id = usr.principal_id
                LEFT JOIN sys.columns AS cl
                    ON cl.column_id = perm.minor_id
                       AND cl.[object_id] = perm.major_id)
    SELECT
        @Permissions = @Permissions + PermissionState + ' ' + Permission
        + ' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ' '
        + ColumnName + ' TO ' + UserName
        + CASE WithOption
            WHEN 'Y' THEN ' WITH GRANT OPTION'
            ELSE ''
          END + ';
'
    FROM
        PermQuery

PRINT @DropSynonym
EXEC(@DropSynonym)

PRINT @CreateSynonym
EXEC(@CreateSynonym)

PRINT @Permissions
EXEC (@Permissions)  

Auto Generate SQL Server All Databases Attach and Detach Scripts

-- Detach All Databases

-- Build the sp_detach_db command (ONLINE, non-system databases only):
SELECT DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('C','D')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')

-----------------------------------------------------------------------------

-- Attach All Databases
-- Build the sp_attach_db:
-- (I preach everyone against using cursor... so I don't)
SET NOCOUNT ON
DECLARE     @cmd        VARCHAR(MAX),
            @dbname     VARCHAR(200),
            @prevdbname VARCHAR(200)

SELECT @cmd = '', @dbname = ';', @prevdbname = ''

CREATE TABLE #Attach
    (Seq        INT IDENTITY(1,1) PRIMARY KEY,
     dbname     SYSNAME NULL,
     fileid     INT NULL,
     filename   VARCHAR(1000) NULL,
     TxtAttach  VARCHAR(MAX) NULL
)

INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),'') AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
            WHERE SUBSTRING(filename,1,1) IN ('C','D'))
            AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
            AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
ORDER BY dbname, fileid, filename

UPDATE #Attach
SET @cmd = TxtAttach = 
            CASE WHEN dbname <> @prevdbname
            THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''')
            ELSE @cmd
            END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''',
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
    @dbname = dbname
FROM #Attach  WITH (INDEX(0),TABLOCKX)
 OPTION (MAXDOP 1)

SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
 GROUP BY dbname) AS x

DROP TABLE #Attach