SQL SERVER E-NEWSLETTER for June 13, 2002

--------------------------------------------
Replicate strings in T-SQL
--------------------------

The REPLICATE T-SQL command replicates a given string a certain number
of times. The string may be a single character or several characters.
Using this command you can create output as reports or insert it in scripts
for more readable output. The following command will output a string of
asterisks:

PRINT REPLICATE('*',80)

To output a string that will line up fixed width character output, such
as the courier font, use the following command, which will generate the
desired result. It demonstrates how to visually find the length of a SQL
Server instance name. This is a simple demonstration but one that is
invaluable for reading T-SQL output.

PRINT REPLICATE('         |',10)
PRINT REPLICATE('1234567890',10)
PRINT @@SERVICENAME

The following is a sample script in which output is displayed showing at
what point the script is currently:

SET NOCOUNT ON
GO
PRINT REPLICATE('#',20) + '  CREATING MyUser Schema ' + REPLICATE('#',20)
GO
PRINT REPLICATE('*',20) + '  ADDING LOGINS...'
GO
EXEC sp_addlogin MyUser, NULL
GO
PRINT REPLICATE('*',20) + '  ADDING USERS...'
GO

EXEC sp_adduser MyUser
GO
PRINT REPLICATE('*',20) + '  ADDING ROLE
MEMEBERS...'
GO
EXEC sp_addrolemember db_ddladmin, MyUser
GO
PRINT REPLICATE('*',20) + '  CREATING TABLES...'
GO
CREATE TABLE MyUser.x_temp
(KEY1 INT IDENTITY NOT NULL
     CONSTRAINT pk_x_temp PRIMARY KEY,
COL1 INT NULL)
GO
SELECT type, name MyUserTables FROM sysobjects WHERE USER_NAME(uid) = 'MyUser'
GO
PRINT REPLICATE('#',20) + '  DROPPING MyUser Schema ' + REPLICATE('#',20)
GO
PRINT REPLICATE('*',20) + '  DROPPING TABLES...'
GO
DROP TABLE MyUser.x_temp
GO
SELECT type, name MyUserTables FROM sysobjects WHERE USER_NAME(uid) = 'MyUser'
GO
PRINT REPLICATE('*',20) + '  DROPPING ROLE MEMEBERS...'
GO
EXEC sp_droprolemember db_ddladmin, MyUser
GO
PRINT REPLICATE('*',20) + '  DROPPING USERS...'
GO
EXEC sp_dropuser MyUser
GO
PRINT REPLICATE('*',20) + '  DROPPING LOGINS...'
GO
EXEC sp_droplogin MyUser
GO

When adding this to scripts, it's easier to troubleshoot where potential
problems have occurred when building databases. If you receive an error
within the script, you will be able to identify where in the script the
error occurred.

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