Is Full Text Installed in your SQL server

 SELECT 

    CASE 
         WHEN 
             FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1 
         THEN 
              'INSTALLED' 
         ELSE 
              'NOT INSTALLED' 
    END [Is Full Text Installed?]

Get all databases a login can access

-- First Check you have access in all the databases in your system..

SELECT s.* FROM master.sys.databases s WHERE HAS_DBACCESS(s.name) = 1

-- List all the databases a login can access in the system.

EXECUTE AS LOGIN = 'Sl_Mwbe_write'  

SELECT s.name FROM master.sys.databases s

WHERE HAS_DBACCESS(s.name) = 1

REVERT

SQL server browsing history in SSMS

Where do we the file, which stores the history of sql servers which we browse through SSMS.

C:\Users\[YourUserName]\AppData\Roaming\Microsoft\SQL Server Management Studio

From there you must browse to your SQL version….

You can see the file name called SqlStudio.bin

You can also transfer the files in between servers..

You just need to take the backup at the source server, transfer to the destination server and replace the file in the same file location.
(Don’t forget to take the back up in destination server file as well, just in case)

Script to pull database user and their roles, permissions

DECLARE
    @sql VARCHAR(2048)
    ,@sort INT

DECLARE tmp CURSOR FOR


/*********************************************/
/*********   DB CONTEXT STATEMENT    *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********     DB USER CREATION      *********/
/*********************************************/

SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
        3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
        4 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

UNION

/*********************************************/
/*********    DB ROLE PERMISSIONS    *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
        5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
        6 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) IN ( 
                                                --get user names on the database
                                                SELECT [name]
                                                FROM sys.database_principals
                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                                                and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                                              )
--ORDER BY rm.role_principal_id ASC


UNION

SELECT '' AS [-- SQL STATEMENTS --],
        7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********  OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc
            ELSE 'GRANT'
        END
        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
        + CASE
                WHEN cl.column_id IS NULL THEN SPACE(0)
                ELSE '(' + QUOTENAME(cl.name) + ')'
          END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
        + CASE
                WHEN perm.state <> 'W' THEN SPACE(0)
                ELSE SPACE(1) + 'WITH GRANT OPTION'
          END
            AS [-- SQL STATEMENTS --],
        9 AS [-- RESULT ORDER HOLDER --]
FROM   
    sys.database_permissions AS perm
        INNER JOIN
    sys.objects AS obj
            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
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC



UNION

SELECT '' AS [-- SQL STATEMENTS --],
    10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********    DB LEVEL PERMISSIONS   *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
        END
    + SPACE(1) + perm.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
    + CASE
            WHEN perm.state <> 'W' THEN SPACE(0)
            ELSE SPACE(1) + 'WITH GRANT OPTION'
      END
        AS [-- SQL STATEMENTS --],
        12 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser

WHERE   [perm].[major_id] = 0
    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        13 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        14 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
            END
                + SPACE(1) + perm.permission_name --CONNECT, etc
                + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
                + QUOTENAME(SCHEMA_NAME(major_id))
                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
                + CASE
                    WHEN perm.state <> 'W' THEN SPACE(0)
                    ELSE SPACE(1) + 'WITH GRANT OPTION'
                    END
            AS [-- SQL STATEMENTS --],
        15 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
    inner join sys.schemas s
        on perm.major_id = s.schema_id
    inner join sys.database_principals dbprin
        on perm.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema


ORDER BY [-- RESULT ORDER HOLDER --]


OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT @sql
        FETCH NEXT FROM tmp INTO @sql, @sort   
END

CLOSE tmp
DEALLOCATE tmp

How to find your IP is static or Dynamic

Open command prompt..
type  IPconfig /all

If you find DHCP Enabled ....:  Yes,  -- it says your IP is Dynamic
                                                :   No,  -- It says your IP is Static

Simple Dynamic SQL to get system tables information

Simple Dynamic SQL to get all tables row count from the database.
You have many number of ways to get this information.

SELECT 'SELECT COUNT(*) AS '+ name +' FROM ' + name AS TableName
FROM sys.tables


You can also apply the condition to the sort the data...
WHERE name like 's%'

Restore/Attach the database with out Log(ldf) file.

-- You can attach a database to the server just with mdf even though you dont have ldf.

USE [master] GO
EXEC sp_attach_single_file_db @dbname='AdventureWorksDW2012',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_Data.mdf'
GO

-- You can also use the following method..
USE [master] GO
CREATE DATABASE AdventureWorksDW2012_New ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_New_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO

-- You can also use the following method.
USE [master] GO
CREATE DATABASE AdventureWorksDW2012_New1 ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_New1_Data.mdf')
FOR ATTACH
GO

In all the above cases the ldf file created by itself in default log files folder.

SQL server error during the installation: Unhandled Exception.. System ConfigurationException

Its frustrating sometimes when more issues coming during your SQL server installation.

I have been told to installation SQL server on a machine. As I don't have proper permissions I had to try couple of times install/ uninstall the SQL server on the same machine...
Finally, I got the permissions and thought it will go smooth from now. But suddenly I got the following error during my next try...



Solution:
If your operating system is Windows 8, 7, Vista:
C:\Users\[USERNAME]\AppData\Local\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0

If Windows XP:
C:\Documents and Settings\[USERNAME]\local settings\application data\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0
drive to that folder and Rename the file "user.config" to "user.config.bak" (no quotes).

Reason: Every time you try the install SQL,  it try to create 'user.config' file in the above folder(vary from OS to OS). During your last uninstall SQL it didn't clean up properly from the folder...
So as it tries to create every time during the installation, it tried creating the file and rejected as the file already exists in the folder.. So please rename it.

Delete Selected list of tables from the SQL database

I have selected tables list which I need to delete from my database. If 10 or less I can do it manually in different ways.
But it has more than 1000 tables which required to delete from the database.

Of course people has their own choice to do this. I have implemented the following script.

First you have to send that list of table names into a temp table.
You may give the Serial number for each row..You can done this by adding extra column with identity property (OR) use
Row_Number() function on the existed column.

Once you are ready with a table with the list of table names(I named it AA_DeletedTables) You can follow the script below.

   
DECLARE @TableName VARCHAR(50)
DECLARE @SQL VARCHAR(100)
DECLARE @i INT =1
DECLARE @Count INT
SELECT @Count = COUNT(*) FROM AA_DeleatedTables

WHILE (@i < =  @Count)
    BEGIN
        SELECT @TableName = name from AA_DeleatedTables WHERE Sno = @i
        SET @SQL = 'Drop table '+@TableName
        --PRINT (@SQL)  You can test by enabling this by just printing the test
        EXEC @SQL
       
        SET @i = @i +1
    END

--SELECT * FROM AA_DeleatedTables

Drop table AA_DeleatedTables
NOTE: It will work for when the tables doesn't have any constraints referencing..
If you want you can use this table as audit table by adding time stamp columns....

Deleting Old backup files from the file system by using SQL script

Some times you may frustrate to use maintenance plans. Some times complete work by using T-SQL scripts is better(I usually go for the scripts) than maintenance plans.
If you want to delete the backup files from the filesystem, You can do by using T-SQL script.
Please follow the below script and pass the parameters accordingly your server/system.

DECLARE @DeletedDate DATETIME
            SET @DeletedDate = DateAdd(day, -15, GetDate())  -- Its your setting the number of days
            EXECUTE master.sys.xp_delete_file
            0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
            N'E:\FullBackup\', -- folder path (trailing slash)
            N'bak', -- file extension which needs to be deleted (no dot)
            @DeletedDate, -- date prior which to delete
            1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

Jobs running on the SQL server and get the next run

List of the jobs and when and what time they execute for the next time...
You can use the following script to find that information....


SELECT sj.name AS Name,sjs.next_run_date AS NextRunDate,LEFT(RIGHT('000000' + CAST(sjs.next_run_time AS VARCHAR(6)), 6),2)
                                    + ':' + SUBSTRING(RIGHT('000000'+ CAST(sjs.next_run_time AS VARCHAR(6)),6), 3, 2)
                                    + ':' + RIGHT(RIGHT('000000' + CAST(sjs.next_run_time AS VARCHAR(6)),6), 2) AS NextRunTime,
        sj.date_created AS DateCreated, sj.date_modified AS DateModified,sj.description AS Description
FROM msdb..sysjobschedules sjs JOIN
      msdb..sysjobs sj on sjs.job_id = sj.job_id
  --WHERE description not like '%This job is owned by a report%'
ORDER BY 3 --Name

History of backups for the selected database

If you want to see the backup history of  the selected database or history for all of the databases.
Please use the following script.

SELECT
     b.database_name,
     m.physical_device_name AS 'Location_Of_Backups',
     CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) AS 'BackupSize_In_MBs',
     CAST(DATEDIFF(second, b.backup_start_date, b.backup_finish_date) AS VARCHAR(4)) AS 'Duration_In_Secs',
     b.backup_start_date AS'Stareted',
     b.backup_finish_date AS'Finished',
     CAST(b.first_lsn AS VARCHAR(50)) AS 'First_LSN',
     CAST(b.last_lsn AS VARCHAR(50)) AS 'Last_LSN',
     CASE b.[type]
              WHEN 'D' THEN 'Full Backup'
             WHEN 'I' THEN 'Differential Backup'
             WHEN 'L' THEN 'Transaction Log Backup'
     END AS BackupType,
     b.server_name,
     b.recovery_model
FROM msdb.dbo.backupset b
INNER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE b.database_name = DB_NAME() -- For the current database
ORDER BY backup_start_date DESC, backup_finish_date
GO
 -- 

Full and Differential Backups in a fashion

One day, I got a requirement for the new server backup strategy.. Of course I have done this before but didn't carry the scripts with me as I always feels I can implement on the fly..
But this time I just want to save this script and may useful for others.
Requirement : 
               Full backup has to run every 'SUNDAY' and Differentials  should have to run every day.
Everything in the same script...
 The reason why I did both in the same script is to not fail the job on any weekday as the existing database refreshed or new database created.
Reason to fail: As the 'new database' / 'refreshed' haven't had the Full backup yet, The direct diff. backups would be fails.

So,  this script will check for the full backup on every day for every database.

    DECLARE @DBName VARCHAR(40)
    DECLARE @SQL VARCHAR(max)

    DECLARE DataBaseName CURSOR FOR
            SELECT s.name  FROM sys.databases s where s.database_Id > 4
             --s.name IN ('MyDB1','MyDB2') -- You can name your own databases here
   
    OPEN DataBaseName

    FETCH NEXT FROM DataBaseName 
    INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN

    DECLARE @BackName VARCHAR(50) = @DBName +'_'+REPLACE(CONVERT(VARCHAR(10),GETDATE(),101),'/','') -- I want this format MyDB1_MMDDYYYY
    DECLARE @BackPath VARCHAR(500)
   

    IF (DATENAME (DW,GETDATE()) = 'SUNDAY'
                        OR      -- Will check whether the full backup happend at all on the database
                        (SELECT ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup
                        FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
                        ON A.database_name = B.name AND A.type = 'D'
                        WHERE B.name  = @DBName
                        ) = 'NEVER'
                        )
       
        BEGIN
            SET  @BackPath = 'E:\FullBackup\'+ @BackName

            SET @SQL = 'BACKUP DATABASE ['+@DBName+'] TO  DISK = N'''+@BackPath+'.bak'' WITH NOFORMAT, NOINIT,  NAME = N'''+@DBName+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
           
            declare @backupSetId as int
            select @backupSetId = position from msdb..backupset where database_name=N'''+@DBName+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'''+@DBName+''' )
            if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database '''''+@DBName+''''' not found.'', 16, 1) end
            RESTORE VERIFYONLY FROM  DISK = N'''+@BackPath+'.bak'' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND'
           

            EXEC (@SQL)

        END
    ELSE
        BEGIN
           
            --If(DB <> FullBackup)
            --    take full backup
            SET  @BackPath = 'E:\DifferentialBackup\'+ @BackName
            SET @SQL = 'BACKUP DATABASE ['+@DBName+'] TO  DISK = N'''+@BackPath+'_Diff.Bak'' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'''+@DBName+'-Differential Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
           
            declare @backupSetId as int
            select @backupSetId = position from msdb..backupset where database_name=N'''+@DBName+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'''+@DBName+''' )
            if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database '''''+@DBName+''''' not found.'', 16, 1) end
            RESTORE VERIFYONLY FROM  DISK = N'''+@BackPath+'_Diff.Bak'' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND'
           

            EXEC (@SQL)
        END
    FETCH NEXT FROM DataBaseName INTO @DBName

    END
    CLOSE DataBaseName;
    DEALLOCATE DataBaseName;

    GO



Now, the real problem is space.... The backups keep on going We are not overwriting / Deleting yet.. So, the below script may useful for the automation. Otherwise you have do delete the files manually...
You can attach the following script with the above script..


IF (DATENAME (DW,GETDATE()) = 'SUNDAY' )
    BEGIN
             --Only sunday We can delete the old FullBackup files...
            DECLARE @DeleteDateFull DATETIME
            SET @DeleteDateFull = DateAdd(day, -15, GetDate())  -- Its your setting the number of days
            EXECUTE master.sys.xp_delete_file
            0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
            N'E:\FullBackup\', -- folder path (trailing slash)
            N'bak', -- file extension which needs to be deleted (no dot)
            @DeleteDateFull, -- date prior which to delete
            1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

                 --Only sunday We can delete the old Differential backup files...
            DECLARE @DeleteDateDiff DATETIME
            SET @DeleteDateDiff = DateAdd(day, -15, GetDate()) -- Its your setting the number of days
            EXECUTE master.sys.xp_delete_file
            0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
            N'E:\DifferentialBackup\', -- folder path (trailing slash)
            N'bak', -- file extension which needs to be deleted (no dot)
            @DeleteDateDiff, -- date prior which to delete
            1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)
    END

LogSpace used for all the databases

-- LogSpace used for all the databases

SET NOCOUNT ON;
DECLARE @tran_log_space_usage table(
        database_name sysname
,       log_size_mb float
,       log_space_used float
,       status int
);
INSERT INTO @tran_log_space_usage
EXEC('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');

SELECT
    database_name,
    log_size_mb,
    log_space_used,
    status  
FROM @tran_log_space_usage

Deleting old backup files from the file system using T-SQL

Some times it is very useful to have a script to delete the old files which are increasing the space keeping in the drive.. We always delete them manually..We may forget some times and resulting automatic backups may fail... So have this script and automate along with the backup script to solve space issues..

DECLARE @DelDate DATETIME
SET @DelDate = DateAdd(day, -14, GetDate()) -- The date where you want to delete files upto...

EXECUTE master.sys.xp_delete_file
1,        -- File Type Selected  (0 = FileBackup, 1 = FileReport)
N'E\Backups\', -- folder path where you are deleting the files from ending with slash
N'bak', -- file extension which needs to be deleted, dont required the dot(.)
@DelDate,
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

Create a folder in file system using T-SQL

--I) How to create a folder in our file system using T-SQL

DECLARE @SQL VARCHAR(500)
DECLARE @DestPath VARCHAR(200)



SET    @DestPath = 'E\SQLFolder\'
SET    @SQL =    'MKDIR ' + @DestPath +'YourFolderName'

EXEC    xp_cmdshell    @SQL, no_output
PRINT    @SQL
EXEC (@SQL)

Note: Please make sure 'XPCmdSheeEnabled' True.

Seperate the Unique String value into multiple with comma seperator

I have a requirement saying I want to pass a string as parameter and should get the results by splitting string by each Comma(,) and load them in to a table
/*                   CODE HERE

SET NOCOUNT ON

DECLARE @MyTask TABLE (Result VARCHAR(25))
DECLARE @String VARCHAR(500) = 'SQL,Oracle,MySQL,DB2,NoSQL'
DECLARE @SplitValues VARCHAR(25)

WHILE LEN(@String) > 0
BEGIN
    IF PATINDEX('%,%',@String) > 0
    BEGIN
        SET @SplitValues = SUBSTRING(@String, 0, PATINDEX('%,%',@String))
        INSERT INTO @MyTask
            SELECT @SplitValues

        SET @String = SUBSTRING(@String, LEN(@SplitValues + ',') + 1,LEN(@String))
    END
    ELSE
    BEGIN
        SET @SplitValues = @String
        SET @String = NULL
        INSERT INTO @MyTask
            SELECT @SplitValues
    END
END
SELECT * FROM @MyTask

*/
You may pass the parameter(Unique string) with any kind of Separator. You should be make sure with it during the implementation.

SQL Puzzle

 My friend came to me and can you find this word 'S L U N O I O T'. I said 'SOLUTION'.
Then I thought why can't I create a script which will jigjag a input word.

Of course you may do so many other ways.. I have developed like below. If you want to use this for longer strings please change the string length appropriately...


SET NOCOUNT ON
GO
Declare @str VARCHAR(20) = 'SOLUTION' -- You can pass your own word here
DECLARE @val CHAR(1)

DECLARE @valstr VARCHAR(20)
DECLARE @len INT = LEN(@str)
Declare @mytab Table (ID INT IDENTITY,Value char(1),OrderID INT)
DECLARE @i int =1
    WHILE (@len >= @i)
    BEGIN
        SET @val = SUBSTRING(@str,@i,1)
        INSERT INTO @mytab VALUES (@val,rand()*10)
        SET @valstr = SUBSTRING(@str,@i,@len)
        --SET @len = Len(@valstr)
        SET @i = @i +1
    END

SELECT *, row_number() OVER (ORDER BY orderID) RowID into #temp from @mytab


DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + value
FROM #temp
 SELECT replace(@names,',','') As WhatIsThisWord
 drop table #temp

Note: Please acknowledge.

Database Snapshot

                                                --Database Snapshot
A database snapshot is a read-only, static view of a SQL Server database. A database snapshot always resides on the same server instance as its source database.


USE master
GO
-- Drop database snapshot if it already exists
IF  EXISTS (
         SELECT name
         FROM sys.databases
         WHERE name = 'MYDB_snapshot'
                   )
DROP DATABASE MYDB_snapshot
GO
-- Create the database snapshot
CREATE DATABASE MYDB_snapshot ON
( NAME = MYDB , -- FileName(Data)
FILENAME =
'C:\PYamani\SQL_Archives\MYDB_snapshot.ss' )
AS SNAPSHOT OF NJSBCL;
GO

-- Dropping the SnapShort database

DROP DATABASE NJSBCL_snapshot1

Notes:

• The source database must be in Online.
• To create a database snapshot on a mirror database, the database must be in the SYNCHRONIZED mirroring state.
• All recovery models support database snapshots.
• You cannot Drop a database,Detached or restored which holds the at least one Snapshot.
• Files cannot be dropped from the source database or from any snapshots.
• You cannot take a backup for the snapshot database.
• You can have 'N' number of snapshots on a single database, It is depends on the disc space.

    Each database snapshot persists until it is explicitly dropped by the database owner.

-- Testing snapshot by creating new tables
    Once you create a snapshot, you can create a table on source/base database. You can refresh the snapshot tables now to see the new table.
        Create table TestTable (Sno INT)
        Drop table TestTable

Disconnect/Kill all the SPID's in the database

Some times a situation like to restore the database immediately.
When you are trying to do that, It will not let you install and throwing the error saying Database is in use....
Once you find the SPID's running under the database, It may be hard to disconnect all of them in single short (Of course you can use Single user mode, It has its own problems though).

By running the following script you can kill all the SPID's running under the desired database and you can restore the database by adding restore database script.
Please take a look at it,


DECLARE @ID INT
DECLARE @MSG CHAR(8), @DataName SYSNAME = 'newdb'

WHILE 1 = 1
BEGIN
 SET ROWCOUNT 1
 SELECT  @id = spid
        FROM Master..Sysprocesses P,
                Master..Sysdatabases D
         WHERE D.Name=@DataName AND
                D.dbid = P.dbid
IF @@rowcount = 0
 BREAK

 SELECT @msg = 'KILL ' + CONVERT(CHAR(8) ,@ID)

PRINT @msg
EXEC( @msg)

END

-- Please continue your restore script here.