ÿþ<html> <head> <title>Albert van der Sel Overview of some often used SQL Server TSQL code</title> </head> <body bgcolor="#FFFFFF" link="blue" alink="blue" vlink="blue"> <h1>Overview of some often used SQL Server TSQL code</h1> <B>Version</B> : 3.7<br> <B>Date</B> : 15/02/2012<br> <B>By</B> : Albert van der Sel<br> <hr/> <ul> <li><B>Type of doc</B>: It's just a simple listing of some often used SQL Server 2005/2008 SQL and TSQL Statements.</li> <li><B>For who</B> : For anyone who likes a simple list of easy examples.</li> <li><B>Best Usage </B>: Just browse around, or use "find/search" to look for a keyword or identifier, or use the menu below.</li> </ul> <font face="arial" size=2 color="black"> This document lists some often used TSQL code and statements. It's, ofcourse, not much compared <br> to extensive documentation like for example "Books Online" or "BOL".<br> But maybe it's convienient to have some often used code "close together" in an easy to browse document.<br> Mostly, it shows commands only, with only minimal comment.<br> <br> Note: In TSQL code, anything behind "--", or between "/* */", is comment.<br> <br> <hr/> <font face="arial" size=2 color="blue"> <B>Main Contents:</B><br> <br> <B> 1. Code and Examples:<br> <br> <A href="#section1"> 1.1. GET A LISTING OF FILE AND FILEGROUP INFORMATION </A><br> <A href="#section2"> 1.2. EXAMPLE OF CREATING A DATABASE USING A TSQL SCRIPT</A><br> <A href="#section3"> 1.3. CREATING A SQL SERVER LOGIN AND A WINDOWS LOGIN (meaning: creating accounts)</A><br> <A href="#section4"> 1.4. SOME DICTIONARY QUERIES FOR VIEWING SESSIONS, AND LOCKS, AND BLOCKING SESSIONS</A><br> <A href="#section5"> 1.5. SOME DICTIONARY QUERIES FOR VIEWING WAITS</A><br> <A href="#section6"> 1.6. SOME EXAMPLES OF USING xp_cmdshell</A><br> <A href="#section7"> 1.7. QUICKLY CREATE A COPY TABLE FROM AN EXISTING TABLE (WITH ALL DATA)</A><br> <A href="#section8"> 1.8. A FEW NOTES ABOUT "SCRIPTS", AND STORED PROCEDURES AND FUNCTIONS</A><br> <A href="#section9"> 1.9. CREATING A "LOOP" IN A SCRIPT, OR PROCEDURE, USING A CURSOR</A><br> <A href="#section10"> 1.10. HOW TO KILL A SESSION</A><br> <A href="#section11"> 1.11. HOW TO FIND AN OBSTRUCTING, OR BLOCKING, SESSION</A><br> <A href="#section12"> 1.12. SOME OFTEN USED STANDARD FUNCTIONS</A><br> <A href="#section13"> 1.13. REMOVING "UNWANTED" CHARACTERS FROM A FIELD OF A TABLE, OR STRING</A><br> <A href="#section14"> 1.14. REMOVING "UNWANTED" QUOTES FROM A FIELD OF A TABLE, OR STRING</A><br> <A href="#section15"> 1.15. SIMPLE OVERVIEW MOST IMPORTANT SQL SERVER DATATYPES</A><br> <A href="#section16"> 1.16. THE CAST AND CONVERT CONVERSION FUNCTIONS</A><br> <A href="#section17"> 1.17. A FEW WORDS ON GRANTING ROLES AND PERMISSIONS TO LOGINS (users) USING TSQL </A><br> <A href="#section18"> 1.18. A FEW WORDS ON SHOWING PERMISSIONS USING TSQL </A><br> <A href="#section19"> 1.19. GET A LISTING OF ALL COLUMNNAMES WITH ALL TABLES, AND DATATYPES, USING TSQL </A><br> <A href="#section20"> 1.20. A FEW NOTES ABOUT THE "@@" FUNCTIONS</A><br> <A href="#section21"> 1.21. HOW TO SCRIPT YOUR DATABASE</A><br> <A href="#section22"> 1.22. HOW TO GENERATE THE INSERT STATEMENTS FOR A TABLE WITH DATA</A><br> <A href="#section23"> 1.23. OVERVIEW SQL SERVER VERSIONS AND BUILDS v 7 - 2008</A><br> <A href="#section24"> 1.24. LOGGING FROM STORED PROCEDURES AND TSQL</A><br> <A href="#section25"> 1.25. USING EXEC IN TSQL CODE</A><br> <A href="#section26"> 1.26. HOW TO VIEW WHETHER PROCESSES (SPIDS) WERE NTLM OR KERBEROS AUTHENTICATED</A><br> <A href="#section27"> 1.27. A FEW NOTES ON USER DEFINED FUNCTIONS</A><br> <A href="#section28"> 1.28. HOW TO GET A LIST OF PK AND FK CONSTRAINTS</A><br> <A href="#section29"> 1.29. A FEW NOTES ON DYNAMIC SQL</A><br> <A href="#section30"> 1.30. A FEW NOTES ON BACKUP AND RESTORE USING TSQL STATEMENTS</A><br> <A href="#section31"> 1.31. A FEW NOTES ON REVIVING SQL ACCOUNTS AFTER MOVE (OR RESTORE) OF A DATABASE</A><br> <br> 2. Some further exercises:<br> <br> <A href="#section33"> 2.1. Excercise 1: SIMPLE EXAMPLE OF CODE TO GENERATE THE BASIC "CREATE TABLE" STATEMENTS</A><br> <A href="#section34"> 2.2. Excercise 2: SIMPLE EXAMPLE OF AN AUTOMATED TSQL BACKUP SCRIPT</A><br> <br> 3. Old Stuff:<br> <br> <A href="#section35"> 3.1. OLDER txt FILE LISTING DBA SCRIPTS AND STATEMENTS</A><br> </B> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section1">1.1. GET A LISTING OF FILE AND FILEGROUP INFORMATION:</h3><br> <font face="courier" size=2 color="black"> Often you need a listing of your database files and characteristics, like paths, sizes etc..<br> Here are a few queries that will show you that information.<br> <br> <br> <font face="courier" size=2 color="blue"> -- use Yourdatabase_Name<br> -- go<br> <br> <B>-- Overall listing, and names, and free/used space:</B><br> <br> SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],<br> size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]<br> FROM sys.database_files;<br> <br> <B>-- Selected info from sysfiles:</B><br> <br> SELECT sysfiles.fileid, sysfiles.groupid, sysfiles.size,<br> (sysfiles.size * 8 / 1024) AS "SIZE_IN_MB",<br> substring(sysfiles.name, 1, 30) AS NAME, <br> substring(sysfiles.filename, 1, 100) AS FILENAME, <br> substring(sysfilegroups.groupname, 1, 40) AS GROUPNAME<br> FROM sysfiles, sysfilegroups<br> WHERE sysfiles.groupid=sysfilegroups.groupid<br> <br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section2">1.2. EXAMPLE CREATING A DATABASE USING A TSQL SCRIPT:</h3><br> <font face="courier" size=2 color="black"> <B><U>2.1 Example of creating a Database (called "SALES").</U></B><br> <br> CREATE DATABASE SALES<br> ON<br> <B>PRIMARY</B><br> (NAME=salesPrimary,<br> FILENAME='C:\mssql\data\sales\sales.mdf',<br> SIZE=100MB,<br> MAXSIZE=200,<br> FILEGROWTH=20),<br> <B>FILEGROUP salesData</B><br> (NAME=salesData01,<br> FILENAME='D:\mssql\data\sales\salesdata01.ndf',<br> SIZE=100MB,<br> MAXSIZE=200,<br> FILEGROWTH=10),<br> <B>FILEGROUP salesIndex</B><br> (NAME=salesIndex01,<br> FILENAME='E:\mssql\data\sales\salesindex01.ndf',<br> SIZE=100MB,<br> MAXSIZE=200,<br> FILEGROWTH=10)<br> <B>LOG ON</B><br> (NAME=salesLog01,<br> FILENAME='F:\mssql\data\sales\saleslog01.ldf',<br> SIZE=100MB,<br> MAXSIZE=200,<br> FILEGROWTH=10)<br> GO<br> <br> You can adjust such a script to your own needs.<br> <br> Notes: for performance and availability reasons:<br> <br> <ul> <li> The lines in "bold" are the starts of the "filegroup" definitions. The primary filegroup, (with the .mdf file), is supposed<br> to be reserved for the database dictionary (comparable to the system tablespace in Oracle).</li><br> <li> Try to create seperate filegroups to place the tables and indexes on: don't put them into the "primary" (.mdf) file.<br> So, the .mdf file is supposed to be reserved for the dictionary, the .ldf file(s) are for the transaction log, and<br> the ordinary (regular) datafiles (for tables and indexes), uses the .ndf extension.</li><br> <li> Always try to seperate the transactionlog, from the datafiles, on different drives/filesystems.</li><br> <li> Try to create seperate filegroup(s) (containing .ndf files) for holding tables, and indexes.</li><br> </ul> <br> <br> <B><U>2.2 Example of adding a "FileGroup" and file to a Database.</U></B><br> <br> Suppose, for example, that we did not specified an INDEX filegroup for the CREATE statement of the<br> SALES database, as shown above.<br> Now, we can add a seperate filegroup (and file) with the intention to hold the indexes of the database.<br> <br> ALTER DATABASE SALES<br> ADD FILEGROUP [INDEX]<br> <br> ALTER DATABASE SALES<br> ADD FILE <br> (<br> NAME = Sales_Index,<br> FILENAME = 'E:\MSSQL\DATA\SALES\sales_index.ndf',<br> SIZE = 1200MB,<br> MAXSIZE = 1500MB,<br> FILEGROWTH = 50MB<br> )<br> TO FILEGROUP [INDEX]<br> GO<br> <br> Note: You can create, or add, multiple fi..legroups to a database, designated for some purpose.<br> But only you will decide what type of objects will be stored in a certain filegroup.<br> Especially at the "CREATE object ... ON FILEGROUP filegroup_name" statement, will determine on<br> which filegroup an object (like a table or index) will be placed.<br> <br> <br> <B><U>2.3 Some related Systemviews / Dictionary views</U></B><br>: <br> <B>2.3.1 For viewing database characteristics:</B><br> <br> select * from sys.databases<br> <br> select name, database_id, create_date, user_access_desc, state_desc from sys.databases<br> <br> <B>2.3.2 For viewing Filegroup and file information:</B><br> <br> -- Go to the database you are interrested in:<br> -- USE DATABASENAME -- e.g.: use sales<br> -- GO<br> <br> <U>Get all file information:</U><br> <br> SELECT * FROM sys.sysfiles<br> <br> SELECT * FROM sys.database_files<br> <br> <U>Overall listing, and names and free/used space:</U><br> <br> SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],<br> size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]<br> FROM sys.database_files;<br> <br> <U>Selected info from sysfiles:</U><br> <br> SELECT sysfiles.fileid, sysfiles.groupid, sysfiles.size,<br> (sysfiles.size * 8 / 1024) AS "SIZE_IN_MB",<br> substring(sysfiles.name, 1, 30) AS NAME, <br> substring(sysfiles.filename, 1, 100) AS FILENAME, <br> substring(sysfilegroups.groupname, 1, 40) AS GROUPNAME<br> FROM sysfiles, sysfilegroups<br> WHERE sysfiles.groupid=sysfilegroups.groupid<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section3">1.3. CREATE A "SQL SERVER" LOGIN AND A "WINDOWS" LOGIN:</h3><br> <font face="courier" size=2 color="black"> <B><U>3.1 Create a SQL Server user (also called "login"):</U></B><br> <br> This is an internal SQL Server account, independent from Windows authentication.<br> Anyone who knows this account and password, can logon to SQL server itself,<br> and it does not matter on what type of machine or Domain (if applicable) the user is working from,<br> as longs as the user has the client software and all neccesary networking software.<br> So, the account that the real user used to logon to the OS, has nothing to do with the "SQL Server account".<br> <br> CREATE LOGIN login_name WITH PASSWORD=password;<br> <br> Example:<br> <br> CREATE LOGIN webuser WITH PASSWORD='beerisok!'<br> <br> Most important options (there are more):<br> MUST_CHANGE (must change password at first logon)<br> DEFAULT_DATABASE = database<br> DEFAULT_LANGUAGE = language<br> CHECK_EXPIRATION = { ON | OFF}<br> CHECK_POLICY = { ON | OFF}<br> <br> <B>Example:</B><br> <br> CREATE LOGIN webuser WITH PASSWORD='welcome' MUST_CHANGE, CHECK_EXPIRATION=on<br> <br> <B>Example:</B><br> <br> CREATE LOGIN mary WITH PASSWORD='welcome01', DEFAULT_DATABASE=TEST<br> <br> Some options need to be used in combination with others.<br> <br> <B>Example: Creating a SQL Login, and grant access to the TEST database, and grant the login some common roles:</B><br> <br> <font face="courier" size=2 color="blue"> CREATE LOGIN mary WITH PASSWORD='welcome01', DEFAULT_DATABASE=TEST<br> GO<br> USE TEST<br> GO<br> EXEC sp_grantdbaccess 'mary', 'mary'<br> GO<br> EXEC sp_addrolemember 'db_datareader', 'mary'<br> GO<br> EXEC sp_addrolemember 'db_datawriter', 'mary'<br> GO<br> <br> <font face="courier" size=2 color="black"> <br> <B><U>3.2 Create a Windows login:</U></B><br> <br> This time, a user which has been authenticated by Windows (OS logon or Domain logon),<br> needs only be sort of "defined" in SQL Server. The authentication was already done by Windows, so<br> in creating the account, you do not specify a password.<br> In much literature, you see terms like "Windows authentication Mode", or "Trusted connection".<br> In most cases, we are talking about Domain accounts, like "Domain_name\User_name".<br> But also local accounts (of a Server, or other machine) can be added in the same way.<br> <br> CREATE LOGIN [Domain\user | Localmachine\user] FROM WINDOWS;<br> <br> Examples:<br> <br> CREATE LOGIN [ABCCORP\john] FROM WINDOWS;<br> <br> CREATE LOGIN [SERVER1\mary] FROM WINDOWS;<br> <br> Creating logins does not mean those accounts have any permissions in SQL Server.<br> <br> Usually, you grant "roles" to accounts, by which the useraccounts inherit certain permissions.<br> Only in special cases, you grant permissions to individual logins directly.<br> <br> Roles in SQL Server have quite the same functionality as "roles" in other database systems like Oracle.<br> <br> Accounts and roles are also sometimes called "principals".<br> <br> <font face="courier" size=2 color="black"> Note:<br> <br> About granting "roles" and other permissions to logins: please see Section 1.17.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section4">1.4. SOME DICTIONARY QUERIES FOR VIEWING SESSIONS, AND LOCKS, AND BLOCKING SESSIONS:</h3><br> <font face="courier" size=2 color="black"> <B>4.1 VIEWING SESSIONS:</B><br> <br> SELECT session_id, <br> substring(convert(varchar(20),login_time),1,17) AS LOGINTIME,<br> substring(host_name,1,10) AS HOSTNAME,<br> substring(program_name,1,30) AS PROGRAM,<br> substring(login_name,1,10) AS LOGINNAME,<br> substring(nt_user_name,1,10) AS NT_USER,<br> substring(status,1,10) AS STATUS,<br> lock_timeout,<br> row_count<br> FROM <B>sys.dm_exec_sessions</B><br> <br> SELECT spid, cpu, physical_io, blocked, cmd, waittime,<br> substring(convert(varchar(20),last_batch), 1, 20) as "LASTBATCH",<br> substring(nt_username, 1, 15) AS "USERNAME",<br> substring(loginame, 1, 20) AS "LOGINNAME",<br> substring(hostname, 1, 15) AS "HOSTNAME",<br> substring(program_name, 1, 40) AS "PROGRAM" <br> FROM <B>master.dbo.sysprocesses</B><br> <br> Note the field "blocked" in the above query, which also allows you to easily identify blocked sessions.<br> <br> <br> <B>4.2 VIEWING LOCKS AND BLOCKING SESSIONS:</B><br> <br> exec sp_lock<br> <br> SELECT <br> s.login_name, s.nt_user_name,<br> r.session_id AS BLOCKED_SESSION_ID, <br> r.blocking_session_id AS BLOCKING_SESSION_ID,<br> s.program_name,<br> r.start_time,r.status,r.command,database_id,<br> r.wait_type,r.open_transaction_count,r.percent_complete,r.cpu_time,r.reads,r.writes,r.deadlock_priority<br> from <B>sys.dm_exec_sessions s, sys.dm_exec_requests r</B><br> where s.session_id=r.session_id AND blocking_session_id > 0<br> <br> SELECT a.session_id AS blocked_session_id, b.session_id AS blocking_session_id, c.text AS blocking_text, <br> e.wait_type AS blocking_resource, <br> e.wait_duration_ms, d.text AS blocked_text FROM <B>sys.dm_exec_requests</B> a INNER JOIN <B>sys.dm_exec_requests</B> b <br> ON a.blocking_session_id = b.session_id CROSS APPLY <B>sys.dm_exec_sql_text(b.sql_handle)</B> c CROSS APPLY <B>sys.dm_exec_sql_text(a.sql_handle)</B> d <br> INNER JOIN <B>sys.dm_os_waiting_tasks</B> e ON e.session_id = b.session_id<br> <br> SELECT <br> L.request_session_id AS SPID, <br> DB_NAME(L.resource_database_id) AS DatabaseName,<br> O.Name AS LockedObjectName, <br> P.object_id AS LockedObjectId, <br> L.resource_type AS LockedResource, <br> L.request_mode AS LockType,<br> ST.text AS SqlStatementText, <br> ES.login_name AS LoginName,<br> ES.host_name AS HostName,<br> TST.is_user_transaction as IsUserTransaction,<br> AT.name as TransactionName,<br> CN.auth_scheme as AuthenticationMethod<br> FROM sys.dm_tran_locks L<br> JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id<br> JOIN sys.objects O ON O.object_id = P.object_id<br> JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id<br> JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id<br> JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id<br> JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id<br> CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST<br> WHERE resource_database_id = db_id()<br> ORDER BY L.request_session_id<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section5">1.5. SOME DICTIONARY QUERIES FOR VIEWING WAITS:</h3><br> <font face="courier" size=2 color="black"> <B>5.1 View IO statistics on <U>all</U> database files note: (dbid, fileid)=(null,null):</B><br> <br> SELECT * FROM fn_virtualfilestats(null, null)<br> <br> <br> <B>5.2 View IO statistics on all database files of a <U>specific database</U> (dbid, fileid)=(dbid,null):</B><br> <br> -- e.g. like dbid=5:<br> SELECT * FROM fn_virtualfilestats(5, null)<br> <br> <br> <B>5.3 View the top 10 wait statistics:</B><br> <br> -- All<br> <br> select top 10 *<br> from sys.dm_os_wait_stats<br> order by wait_time_ms desc <br> <br> <B>5.4 View the top 10 wait statistics, except for well known wait_types due to system processes</B><br> <br> select top 10 *<br> from sys.dm_os_wait_stats<br> where wait_type not in<br> ( <br> 'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',<br> 'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',<br> 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',<br> 'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', <br> 'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP', <br> 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',<br> 'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',<br> 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', <br> 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'<br> ) <br> order by wait_time_ms desc <br> <br> <br> <B> 5.5 View <U>Signal</U> Waits (cpu) and <U>Resource</U> Waits (other waits like IO, locks etc..):</B><br> <br> SELECT signal_wait_time_ms=sum(signal_wait_time_ms),'%signal (cpu) waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)),<br> resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms),'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)) <br> FROM sys.dm_os_wait_stats<br> <br> <br> <B> 5.6 Most cpu consuming statements:</B><br> <br> select top 10 <br> object_name(st.objectid), db_name(st.dbid), total_worker_time/execution_count AS AverageCPUTime,<br> CASE statement_end_offset <br> WHEN -1 THEN st.text<br> ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2)<br> END AS StatementText<br> from <br> sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st<br> ORDER BY AverageCPUTime DESC<br> <br> <br> <B> 5.7 Top Queries and stored procedures that have been recompiled (plan_generation_num):</B><br> <br> select top 20<br> sql_text.text, sql_handle, plan_generation_num, execution_count, <br> db_name(dbid), object_name(objectid) <br> from <br> sys.dm_exec_query_stats a <br> cross apply sys.dm_exec_sql_text(sql_handle) as sql_text <br> where <br> plan_generation_num >1 <br> order by plan_generation_num desc<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section6">1.6. SOME EXAMPLES USING xp_cmdshell:</h3><br> <font face="courier" size=2 color="black"> With the (external) stored procedure "xp_cmdshell", you can call DOS (cmd) commands and execute OS shell scripts,<br> from your SQL code.<br> <br> To see if it works on your system, try the following from a Query Window:<br> <br> exec xp_cmdshell 'dir c:\'<br> <br> This should provide a listing of files and directories in the root of C:\<br> <br> If instead you see this returned:<br> <br> <font face="courier" size=2 color="red"> Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1<br> SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because<br> this component is turned off as part of the security configuration for this server.<br> A system administrator can enable the use of 'xp_cmdshell' by using sp_configure.<br> For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online. <br> <br> <font face="courier" size=2 color="black"> Then you must 'activate' it, using either "sp_configure" or using the graphical "Surface Area Configuration" utility.<br> <br> <br> <B> 6.1 Enabling "xp_cmdshell" using "sp_configure":</B><br> <br> Copy and paste the following lines of code into a Query Window. Then highlight and execute it.<br> <br> EXEC master.dbo.sp_configure 'show advanced options', 1<br> <br> RECONFIGURE<br> <br> EXEC master.dbo.sp_configure 'xp_cmdshell', 1<br> <br> RECONFIGURE<br> <br> To find out if it works now, try again the following from a Query Window:<br> <br> exec xp_cmdshell 'dir c:\'<br> <br> <br> <B> 6.2 Some examples using "xp_cmdshell":</B><br> <br> <br> <B>Example 1:</B><br> <br> EXEC master.dbo.xp_cmdshell'c:\test\mybatch.cmd'<br> <br> <br> <B>Example 2:</B><br> <br> SELECT @LOGSTRING=@ART_NR+' :'+@ACTION+' '+CONVERT(VARCHAR(64),@ACTION_DATE)<br> SELECT @log_cmd='echo'+' '+@LOGSTRING+' >> C:\TEMP\LOAD_FILE.LOG'<br> <br> EXEC master.dbo.xp_cmdshell @log_cmd<br> <br> <br> <B>Example 3:</B><br> <br> SELECT @totalcommAND='bcp ##BCP_LOAD in '+@importpath+' -c -F2 -T'<br> <br> EXEC @RESULT = master.dbo.xp_cmdshell @totalcommAND<br> IF (@RESULT <> 0)<br> BEGIN<br> SET @MESSAGE='Error loading data in temporary table. Possibly wrong path or file not found.'<br> GOTO error_section<br> END<br> <br> <br> <B>Example 4:</B><br> <br> DECLARE @cmd sysname, @var sysname<br> SET @var = 'Hello world'<br> SET @cmd = 'echo ' + @var + ' > var_out.txt'<br> EXEC master..xp_cmdshell @cmd<br> <br> DECLARE @cmd sysname, @var sysname<br> SET @var = 'dir/p'<br> SET @cmd = @var + ' > dir_out.txt'<br> EXEC master..xp_cmdshell @cmd<br> <br> <br> <B>Example 5:</B><br> <br> DECLARE @FileName varchar(50),<br> @bcpCommand varchar(256)<br> <br> SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')<br> <br> SET @bcpCommand = 'bcp "SELECT * FROM pubs..authors ORDER BY au_lname" queryout "'<br> SET @bcpCommand = @bcpCommand + @FileName + '" -U albert -P password -c'<br> <br> EXEC master..xp_cmdshell @bcpCommand<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section7">1.7. QUICKLY CREATE A COPY TABLE FROM AN EXISTING TABLE (WITH ALL DATA):</h3><br> <font face="courier" size=2 color="black"> Suppose you have a table X with datarows.<br> If you want to create a copy table, with the same columns, datatypes, and all datarows, you can issue<br> a statement like the example below. Note that the copy table Y should NOT exist beforehand.<br> Table Y will be created "on the fly".<br> <br> Example:<br> <br> <B> select * into Y<br> from X<br> </B> <br> This statement will create table Y with the same columns, datatypes, and the same rows, from table X.<br> But table Y will not have similar "constraints" (like Primary Key, Foreign Keys) like table X has.<br> You will only have a duplicate table Y, with the same rows, as table X has.<br> <br> Note:<br> The upper SQL statement is the SQL Server equivalent of the famous Oracle CTAS statement (Create Table As Select).<br> <br> Note: as an alternative:<br> You can easily script table "X", for example, using SSMS. Then when you have the script, change the name of the table to<br> another appropriate name, like "Y" (in this example we ignore any constraints in the script like a possible primary key name).<br> <br> Next, create table Y (execute the script).<br> <br> Next, use the SQL statement:<br> <br> <font face="courier" size=2 color="blue"> INSERT INTO Y<br> SELECT * FROM X<br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section8">1.8. A FEW NOTES ABOUT "SCRIPTS", AND STORED PROCEDURES AND FUNCTIONS:</h3><br> <font face="courier" size=2 color="black"> If you want to run TSQL code, there are several ways to interact to SQL Server, for example:<br> <br> - Starting a graphical "query window" from SSMS (the graphical <U>S</U>QL <U>S</U>erver <U>M</U>anagement <U>S</U>tudio.<br> - From a Operating System prompt, starting a client like "sqlcmd" or "osql".<br> - Other tools/clients using OLEDB or ODBC etc.. to send TSQL and receive resultsets.<br> <br> Using the graphical "Query Window" is probably the most "popular" method.<br> If you are not so familiar with the query window, then just start SMSS and logon to SQL Server.<br> It should not be too difficult to create a "query window", by browsing the various buttons and menu's:<br> In the upper left corner of SSMS, you should find a "New Query" control.<br> <br> Globally speaking, you can create two "types" of plain TSQL code:<br> <br> <ol> <li> Or you just type in TSQL code in the Query Window, and when done, execute it. Ofcourse, you can also write that code in any editor (like notepad, or notepad+ etc..), save it as a file anywhere on the filesystem, and when needed, you can copy/paste it in the Query Window and run the script. In this method, you are just building scripts (sometimes also called TSQL batches).</li><br> <br> <li>You can also write "stored procedures" and "functions". These have a sort of defined format, but the body of the code can be anything you want that procedure or function to do. This time, you always start out with "CREATE PROCEDURE procedure_name AS..." and then follows the body of your code. Big difference with just scripts is, that procedures and functions are stored in the database, and you can grant permissions on them, as well as there are other levels of control. Also, objects like procedures are pre-compiled and parsed, and generally speaking, execute fast.</li><br> </ol> Whether you write scripts, or build procedures, you have the full power of TSQL available, like:<br> IF .. THEN .. ELSE.. statements, WHILE loops, CASE statements, and all other fabulous logic that's build into TSQL.<br> <br> <B><U>8.1 A few examples of Scripts:</U></B><br> <br> <B>Example:</B><br> <br> Suppose you have a database for just playing around.<br> <br> Suppose you have (or create) these PRODUCTS and DAYORDERS tables:<br> <br> CREATE TABLE [dbo].[Products] <br> (<br> Product_id INT NOT NULL,<br> Product_Name VARCHAR(20) NOT NULL,<br> Unit_price DECIMAL(7,2) NOT NULL,<br> No_In_Stock INT NOT NULL,<br> Must_Order CHAR(1) NOT NULL, -- we use it as a boolean field: y or n<br> ) <br> GO<br> <br> CREATE TABLE [dbo].[DayOrders] <br> (<br> Order_id int NOT NULL,<br> Product_id int NOT NULL,<br> Quantity_Sold int NOT NULL,<br> )<br> GO<br> <br> Note: often, as the schema who owns the tables, the database owner (dbo) is used.<br> <br> Let insert a few records for demonstration purposes:<br> <br> insert into products values (1,'bicycle',200.00,5,'N')<br> insert into products values (2,'motoroil',5.75,7,'N')<br> insert into products values (3,'hammer',10.00,4,'N')<br> insert into products values (4,'roadmap',3.00,10,'N')<br> <br> insert into DayOrders values (177,1,4)<br> insert into DayOrders values (178,4,20)<br> insert into DayOrders values (179,3,12)<br> <br> Here we have only a few records per table, but let's imagine, that here we are dealing with thousends of records...<br> <br> The DayOrders table collects what is sold today. But, is the invetory in the products table still sufficient,<br> or should we order new products? Let's say that when (Quantity_Sold - No_In_Stock) > 0, then we must order<br> new items for that particular product. Let's create a script to determine what is the status:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @ProductId INT<br> DECLARE @MaxProductId INT<br> DECLARE @PRODUCT_NAME VARCHAR(20)<br> DECLARE @NO_IN_STOCK INT<br> DECLARE @QUANTITY_SOLD INT<br> <br> SET @ProductId=1<br> SET @MaxProductId=(SELECT MAX(Product_id) from PRODUCTS)<br> <br> WHILE @ProductId <= @MaxProductId<br> BEGIN<br> &nbsp IF exists (select Product_Id from DayOrders where Product_id=@ProductId)<br> <br> &nbsp SET @NO_IN_STOCK=(SELECT NO_IN_STOCK FROM PRODUCTS WHERE Product_id=@ProductId)<br> &nbsp SET @QUANTITY_SOLD=(SELECT QUANTITY_SOLD FROM DAYORDERS WHERE Product_id=@ProductId)<br> &nbsp SET @PRODUCT_NAME=(SELECT PRODUCT_NAME FROM PRODUCTS WHERE Product_id=@ProductId)<br> <br> &nbsp IF (@QUANTITY_SOLD-@NO_IN_STOCK) > 0<br> &nbsp &nbsp BEGIN<br> &nbsp &nbsp PRINT 'FOR PRODUCT '+@PRODUCT_NAME+' WE MUST ORDER '+convert(varchar(10),@QUANTITY_SOLD-@NO_IN_STOCK)+' ITEMS'<br> &nbsp &nbsp END<br> <br> SELECT @ProductId=@ProductId+1<br> <br> END<br> <br> <font face="courier" size=2 color="black"> The result the script produces is:<br> <br> FOR PRODUCT hammer WE MUST ORDER 8 ITEMS<br> FOR PRODUCT roadmap WE MUST ORDER 10 ITEMS<br> <br> By the way, if you have typed in, or through copy/paste, or via another method, loaded a script, if you "highlight" the code<br> with your mouse, after that you can execute it using the "Execute" button in the SSMS menu.<br> Or you can use the Ctrl-E keycombination.<br> <br> Typically, a script starts with a variable declaration like "DECLARE @ProductId INT", where a variablename<br> starts with the "@" character. Also, you must tell SQL Server what the datatype of that variable is.<br> <br> Next, in this script, we assign two variables a certain value, like "SET @ProductId=1", which sets the beginmarker<br> of the loop we will define a few lines down.<br> The endmarker of the loop, is the Maximum of the product_id, so that we can be sure to parse through all product_id's.<br> Next, we actually <B>start the loop</B>, using the "WHILE expression=true" construct.<br> In this case, we want the loop to cycle using "WHILE @ProductId <= @MaxProductId"<br> <br> The main purpose is, just to show you how such a "block of TSQL code" usually "looks" like.<br> <br> Please take notice of the logic like:<br> <br> WHILE (some condition is true)<br> BEGIN<br> &nbsp Some statements<br> END<br> <br> And also take notice of logic like:<br> <br> IF (some condition is true)<br> BEGIN<br> &nbsp Some statements<br> END<br> <br> In most programmatic "languages", the IF syntax is like "IF condition THEN some_statements ELSE other_statements".<br> But in TSQL, we do not have the "THEN". What we can use is (the ELSE branch is optional, and use it if neccessary):<br> <br> IF (some condition is true)<br> &nbsp BEGIN<br> &nbsp &nbsp Some statements<br> &nbsp END<br> ELSE<br> &nbsp BEGIN<br> &nbsp &nbsp Some other statements<br> &nbsp END<br> <br> Also the "BEGIN" and "END" enclosures, are often just optional. You may use them to make code easier to read, but<br> in many constructs (like the IF), it's totally optional.<br> <br> <B>A few other examples of blocks of TSQL code:</B><br> <br> Here are a few other examples to browse through:<br> <br> -- Take a look at this block of TSQL code:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @I INT<br> <br> SET @I=1<br> <br> WHILE @I<5<br> BEGIN<br> &nbsp PRINT 'My boss bumped his head '+convert(varchar(8),@I)+' times, hehe!'<br> SELECT @I=@I+1 -- needed to increment the "counter" of the loop<br> END<br> <font face="courier" size=2 color="black"> <br> Output:<br> <br> My boss bumped his head 1 times, hehe!<br> My boss bumped his head 2 times, hehe!<br> My boss bumped his head 3 times, hehe!<br> My boss bumped his head 4 times, hehe!<br> <br> Question: Why do we see 4 records in the result, and not 5?<br> <br> <br> -- Take a look at this block of TSQL code:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @EMP_NAME VARCHAR(64)<br> <br> SELECT @EMP_NAME=(SELECT emp_name FROM EMPLOYEES WHERE emp_id=2)<br> <br> IF @EMP_NAME='HARRY'<br> &nbsp PRINT 'WE FOUND HARRY'<br> ELSE<br> &nbsp PRINT 'IT IS NOT HARRY'<br> <br> <font face="courier" size=2 color="black"> <br> So depending on the emp_name where emp_id=2, we can find 'HARRY' or possibly another name.<br> <br> <font face="courier" size=2 color="red"> Note:<br> By the way, hopefully it's clear, that after you have "declared" a variable, and after you have assigned a value<br> to that variable, then if you "select" that variable again, the value will be printed (or shown).<br> For example:<br> <br> DECLARE @x VARCHAR(32) -- declaration<br> SELECT @x='abc123'-- assignment of a value<br> SELECT @x -- show the value<br> <br> Then the 'second' SELECT will show you the value of @x. You may also use the PRINT statement to show the value.<br> <font face="courier" size=2 color="black"> <br> <br> <B><U>8.2 A few simple examples of creating Stored Procedures:</U></B><br> <br> Contrary to just TSQL scripts (or blocks of TSQL code), a Stored Procedure, is really stored in the Database!<br> This makes "control", in various ways, a lot better, and it can be used by any (remote) client if needed.<br> Because it's "inside" the database, any user or program (if permitted) can use it.<br> <br> You should create a stored procedure, in general terms, like so:<br> <br> CREATE PROCEDURE procedure_name [optional parameter list]<br> AS<br> YOUR STATEMENTS<br> <br> A few simple examples to get the general idea:<br> <br> -- Take a look at this code:<br> <br> A simple procedure that gets a record from a table, based on a parameter "@cust_id"<br> <br> <font face="courier" size=2 color="blue"> CREATE PROCEDURE stp_GetCustomerInfo @cust_id int<br> <br> AS<br> SELECT Cust_name, city, country<br> FROM CUSTOMERS<br> WHERE Cust_id=@cust_id<br> <br> <font face="courier" size=2 color="black"> -- Take a look at this code:<br> <font face="courier" size=2 color="blue"> <br> CREATE Procedure dbo.stp_insertEmployee<br> @emp_id INT, -- a number of input variables, matching the fields of the target table<br> @emp_name VARCHAR(20),<br> @salary DECIMAL(7,2),<br> @hiredate DATETIME,<br> @gender CHAR(1)<br> <br> AS<br> BEGIN<br> &nbsp INSERT INTO EMPLOYEE<br> &nbsp VALUES<br> &nbsp (@emp_id,@emp_name,@salary,@hiredate,@gender)<br> END<br> <br> <font face="courier" size=2 color="black"> Note: If you created a stored procedure, and if you want to run it from a query tool,<br> use the syntax:<br> <B>exec</B> procedure_name<br> <br> So, in the above examples, you would use:<br> <br> <B>exec stp_GetCustomerInfo 233</B><br> <br> where we used for example 233 as a cust_id.<br> <br> <B>exec stp_insertEmployee 1,'harry',2000,'2011/1/1','M'</B><br> <br> Where we want to insert a record defined with the values "1,'harry',2000,'2011/1/1','M'"<br> <br> If you want to execute a stored procedure which does not have any parameter, just use:<br> <br> <B>exec procedure_name</B><br> <br> <br> <B><U>More on Parameters:</U></B><br> <br> Suppose we create these two simple procedures, which will just print their input variables.<br> <br> <font face="courier" size=2 color="blue"> CREATE procedure usp_proc1 @input1 varchar(32)<br> AS<br> <br> BEGIN<br> PRINT @input1<br> END<br> GO<br> <br> CREATE procedure usp_proc2 @input2 varchar(32)<br> AS<br> <br> BEGIN<br> PRINT @input2<br> END<br> GO<br> <br> <font face="courier" size=2 color="black"> Now let's create a Main routine, that calls both sp's:<br> <br> <font face="courier" size=2 color="blue"> CREATE procedure usp_Main <br> @inp1 varchar(32),<br> @inp2 varchar(32)<br> AS<br> <br> BEGIN<br> <br> exec usp_proc1 @inp1<br> exec usp_proc3 @inp2<br> <br> END<br> GO<br> <br> <font face="courier" size=2 color="black"> Now, you would for example execute usp_main like so:<br> <br> exec usp_Main 'Hi', 'Reader'<br> <br> which would return:<br> <br> <font face="courier" size=2 color="brown"> Hi<br> Reader<br> <br> <font face="courier" size=2 color="black"> You could also create the procedure usp_Main, with two inputvariables, which if they are not set,<br> will revert to "default" values. In the example below, the defaults are @inp1='Hi' and @inp2='Reader'.<br> <br> <font face="courier" size=2 color="blue"> CREATE procedure usp_Main <br> @inp1 varchar(32)='Hi',<br> @inp2 varchar(32)='Reader'<br> AS<br> <br> BEGIN<br> <br> exec usp_proc1 @inp1<br> exec usp_proc3 @inp2<br> <br> END<br> GO<br> <br> <font face="courier" size=2 color="black"> In this case, you could execute the procedure as simply as:<br> <br> exec usp_Main<br> <br> Ofcourse, you can also call the procedure with the two inputvariables as well, thereby not<br> using the defaults.<br> <br> <br> <B><U>8.3 Table variables:</U></B><br> <br> Many programmatic/scripting languages, use the concepts of arrays, or records (or whatever they are called),<br> as "placeholders" for multiple values.<br> In TSQL you can declare a variable as a Table datatype. This is really great, since this variable<br> looks and feels and behaves like a real table.<br> <br> Here is a simple example:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @TABMETA TABLE<br> (<br> OWNER VARCHAR(128),<br> TABLE_NAME VARCHAR(128)<br> )<br> <br> INSERT INTO @TABMETA<br> SELECT TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES<br> <br> SELECT * FROM @TABMETA<br> <br> <font face="courier" size=2 color="black"> This ofcourse has countless applications.<br> <br> This section not even scratched "the surface" of creating TSQL code. But if you would read the other<br> sections below, the topic will be extended somewhat.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section9">1.9. CREATING A SIMPLE LOOP IN A SCRIPT, OR STORED PROCEDURE, USING A CURSOR:</h3><br> <font face="courier" size=2 color="black"> There are several ways to create a "loop" in your scripts (or stored procedures), using TSQL constructs.<br> One of them, is using a socalled "CURSOR".<br> <br> You may view a cursor as some <B>"virtual table"</B>, which you use in your loop, starting at the "top" of the "table",<br> and while cycling the loop, all the way down to the "bottom" of the virtual table, where the loop finishes.<br> <br> As will be explained below, generally, a cursor is not the optimal construct if you will have large datasets.<br> But for "administrative-like" code, usually it's OK.<br> <br> <B>Example 1:</B><br> <br> Take a look at the following example. Suppose I want to rebuild the indexes of all tables in a database.<br> As such, I want to "dynamically" generate all neccessary DBCC DBREINDEX statements for all tables.<br> In SQL Server 2005/2008, the dictionary view "information_schema.tables", contains all table names,<br> as well as other information.<br> So, if I define (or declare) a cursor (the virtual table in memory) as:<br> <br> DECLARE MyCursor CURSOR FOR <br> SELECT table_name FROM information_schema.tables <br> <br> Then, after "opening" the cursor (declaring only, is not enough), I will have that virtual table which holds<br> all tablenames. This I can use for my loop.<br> To get the first tablename from the cursor, can be done using "FETCH NEXT.."<br> When you start grabbing from the cursor, the "FETCH NEXT.." will actually be the first value.<br> Then you start the "WHILE" loop, which will only end if the @@FETCH_STATUS is not equal "0".<br> So, "WHILE @@FETCH_STATUS = 0", means "we keep looping as long as the @@FETCH_STATUS remains 0.<br> Then, when at last, when the "FETCH_STATUS <> 0", we have took the last value from the cursor, and the loop must stop.<br> <br> Take a close look at this code:<br> <br> <font face="courier" size=2 color="blue"> set nocount on<br> DECLARE @TableName varchar(255) <br> <br> DECLARE MyCursor CURSOR FOR <br> &nbsp SELECT table_name FROM information_schema.tables <br> &nbsp WHERE table_type = 'base table' <br> <br> OPEN MyCursor <br> <br> FETCH NEXT FROM MyCursor INTO @TableName <br> WHILE @@FETCH_STATUS = 0 <br> BEGIN <br> &nbsp SELECT 'DBCC DBREINDEX('+@TableName+','+''''',90)'<br> &nbsp FETCH NEXT FROM MYCursor INTO @TableName <br> END <br> <br> CLOSE MyCursor <br> <br> DEALLOCATE MyCursor<br> <br> <font face="courier" size=2 color="black"> You should always CLOSE and DEALLOCATE your cursor, in order to "clear" it from memory.<br> <br> <B>Example 2:</B><br> <br> Suppose you have an EMPLOYEE table, containing for all employees, the emp_id, emp_name and emp_salary.<br> You need to fill a new table "EMP_BONUS", and store in that table the emp_id, and emp_bonus.<br> The emp_bonus is calculated using "emp_salary . 0.1"<br> <br> Actually, this can easily be done using a relatively simple SQL statement, but I just want to demonstrate<br> how to solve this using a cursor.<br> <br> So, you could use the following script:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @EMPID INT <br> DECLARE @SALARY DECIMAL(7,2) <br> DECLARE @BONUS DECIMAL(7,2) <br> <br> DECLARE MyCursor CURSOR FOR <br> &nbsp SELECT emp_id, emp_salary FROM EMPLOYEES <br> <br> OPEN MyCursor <br> <br> FETCH NEXT FROM MyCursor INTO @EMPID, @SALARY <br> WHILE @@FETCH_STATUS = 0 <br> <br> BEGIN <br> <br> &nbsp SELECT @BONUS=(@SALARY * 0.1)<br> <br> &nbsp INSERT INTO EMP_BONUS <br> &nbsp VALUES <br> &nbsp (@EMPID, @BONUS) <br> <br> &nbspFETCH NEXT FROM MyCursor INTO @EMPID, @SALARY <br> <br> END <br> <br> CLOSE MyCursor <br> <br> DEALLOCATE MyCursor<br> <br> <font face="courier" size=2 color="black"> As we have seen, with the use of a cursor, you can create pretty neat loops in your code.<br> The cursor itself is <B>NOT</B> the loop. It acts like a virtual table which defines the "range" that the WHILE loop<br> operates on. So, if you want to pinpoint the actual loop in the code, it's ofcourse the WHILE loop.<br> Notes:<br> <br> (1):<br> There are more "types", or "variations", of cursors, than is shown in this simple document.<br> You can easily see that from this basic TSQL declare syntax:<br> <br> DECLARE cursor_name CURSOR[ LOCAL | GLOBAL ][ FORWARD_ONLY | SCROLL ][ STATIC | KEYSET |<br> DYNAMIC | FAST_FORWARD ][ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ][ TYPE_WARNING ]<br> FOR select_statement[ FOR UPDATE [ OF column_name [ ,...n ] ] ]<br> <br> See for example BOOKS ONLINE for more information.<br> <br> (2):<br> Using a cursor to plough through really large tables, might not be a good idea.<br> Everybody "has different ideas" of what is actually "large", but be carefull.<br> A cursor is great for tables with a number of rows < 500000 (arbitrarily choosen),<br> but using tables with many millions of rows, might prove to be problematic.<br> First, such a cursor is always very memory consuming, and if you declare a cursor using a large table<br> and update that <I>same table</I> in the script, serious locking issues may arise, if users are<br> are modifying that table as well.<br> <br> Not withstanding these warnings, a cursor is great for using in loops, if you have relatively smaller<br> datasets, just as in example 1, for administrative scripts for the DBA.<br> &nbsp <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section10">1.10. HOW TO KILL A SESSION (SPID):</h3><br> <font face="courier" size=2 color="black"> This is easily done from a query tool, like the Query Window in SSMS.<br> If you have found the "spid", that is, the "SQL Server Process ID", (or Session ID as it is nowadays called),<br> that you need to terminate, then issue the command:<br> <br> <font face="courier" size=2 color="blue"> <B>kill @@spid</B><br> <font face="courier" size=2 color="black"> <br> where the @@spid variable is the session id, like for example 178.<br> So, in this case, the command would be:<br> <br> <font face="courier" size=2 color="blue"> <B>kill 178</B><br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section11">1.11. HOW TO FIND AN OBSTRUCTING, OR BLOCKING, SESSION:</h3><br> <font face="courier" size=2 color="black"> Normally, transactions goes so fast that other users will not notice the locking features of SQL Server.<br> Locks (for example a rowlock, or a pagelock) is just a normal mechanism in ensuring integrety in multi-user access.<br> <br> But sometimes a user (or the program the user is using), will hold a lock too long, and thus it might<br> block other users for accessing the resource.<br> <br> Often "killing" the obstructing session might be the only way to let the other users continue their work.<br> How to kill a session was shown in section 1.10.<br> Finding blocking sessions was already shown in section 1.4.<br> <br> But let's emulate a typical case here.<br> <br> <B>Example:</B><br> <br> First, in your test database, create a test table "X" like:<br> <br> CREATE TABLE X<br> (<br> id INT,<br> name VARCHAR(20))<br> <br> Having just one testrecord in X, is enough for our demo, so let's insert this one:<br> <br> INSERT INTO X VALUES (1,'hammer')<br> <br> Now let's create a (standard) SQL login, with permissions in your TEST database:<br> <br> <font face="courier" size=2 color="blue"> CREATE LOGIN mary WITH PASSWORD='welcome01', DEFAULT_DATABASE=TEST<br> GO<br> USE TEST<br> GO<br> EXEC sp_grantdbaccess 'mary', 'mary'<br> GO<br> EXEC sp_addrolemember 'db_datareader', 'mary'<br> GO<br> EXEC sp_addrolemember 'db_datawriter', 'mary'<br> GO<br> <br> <font face="courier" size=2 color="black"> Since mary is granted the db_datareader and db_datawriter roles, she may modify table X.<br> <br> Let's start a session as mary. Go to the OS prompt, and start a "sqlcmd" session:<br> <br> <font face="courier" size=2 color="blue"> C:\> sqlcmd -U mary -P welcome01<br> 1> use TEST<br> 2> GO<br> Changed database context to 'TEST'.<br> 1> BEGIN TRANSACTION<br> 2> UPDATE X<br> 3> SET NAME='bycicle' WHERE ID=1<br> 4> GO<br> <br> (1 rows affected)<br> <br> <font face="courier" size=2 color="black"> Note that mary started a TRANSACTION, but not committed it yet. This means she holds an exclusive rowlock<br> on that specific record in table X.<br> <br> Now you start a seperate independent Query Window in SSMS (or also use a sqlcmd session).<br> Try to select the table X.<br> <br> SELECT * FROM x<br> <br> Your query will excecute, but no resultset will show. Your session is blocked by the session of mary.<br> <br> Let's view the blocking lock: (query is usable in 2000/2005/2008)<br> <br> <font face="courier" size=2 color="blue"> SELECT spid, cpu, physical_io, blocked, cmd, waittime,<br> substring(convert(varchar(20),last_batch), 1, 20) as "LASTBATCH",<br> substring(nt_username, 1, 15) AS "USERNAME",<br> substring(loginame, 1, 20) AS "LOGINNAME",<br> substring(hostname, 1, 15) AS "HOSTNAME",<br> substring(program_name, 1, 40) AS "PROGRAM" <br> FROM master.dbo.sysprocesses<br> <br> <font face="courier" size=2 color="black"> This should cleary show that your session is blocked by mary's session.<br> <br> Also, let's try the following query (query is usable in 2005/2008)<br> <br> <font face="courier" size=2 color="blue"> SELECT <br> s.login_name, s.nt_user_name,<br> r.session_id AS BLOCKED_SESSION_ID, <br> r.blocking_session_id AS BLOCKING_SESSION_ID,<br> s.program_name,<br> r.start_time,r.status,r.command,database_id,<br> r.wait_type,r.open_transaction_count,r.percent_complete,r.cpu_time,r.reads,r.writes,r.deadlock_priority<br> from sys.dm_exec_sessions s, sys.dm_exec_requests r<br> where s.session_id=r.session_id AND blocking_session_id > 0<br> <br> <font face="courier" size=2 color="black"> That query will show your blocked session id, and the offending session id.<br> <br> To end the lock, you might consider killing that blocking session id, with:<br> <br> kill blocking_session_id<br> <br> Like for example:<br> <br> kill 52<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section12">1.12. SOME OFTEN USED STANDARD FUNCTIONS:</h3><br> <font face="courier" size=2 color="black"> In this section, it's handy to have a small table with a few demo records,<br> so, let's make the following demo table in a test database:<br> <br> <font face="courier" size=2 color="blue"> CREATE TABLE B<br> (<br> id INT,<br> name VARCHAR(100))<br> <br> <font face="courier" size=2 color="black"> Now, let's insert these few test records:<br> <br> <font face="courier" size=2 color="blue"> INSERT INTO B VALUES (1,'123:BB:Johnson') <I>-- note that there is a sort of "code" before the name Johnson</I><br> INSERT INTO B VALUES (2,'&nbsp Carlson') <I>-- note that there is white space before the name Carlson </I><br> INSERT INTO B VALUES (3,'I want a lot of gold in my bathroom')<br> <br> SELECT * FROM B<br> <br> Resultset:<br> <br> id &nbspname<br> 1 &nbsp 123:BB:Johnson<br> 2 &nbsp &nbsp Carlson<br> 3 &nbsp I want a lot of gold in my bathroom<br> <br> <font face="courier" size=2 color="brown"> For any of the following functions, it's true that they can operate on fields of a table in a SELECT query, and<br> they can equally well operate on a variable.<br> <br> <font face="courier" size=2 color="black"> For example:<br> <br> <B>SELECT substring(name, 1, 10) from EMPLOYEE</B><br> <br> Here, we used the substring() function to retrieve just a <I>part</I> of the name field of the EMPLOYEE table in a SELECT query.<br> <br> <B>SELECT @smallvar=substring(@bigvar,10,5)</B><br> <br> In the above statement, we assigned the "@smallvar" variable, a value that's just a part of the "@bigvar" variable.<br> <br> <br> <font face="courier" size=2 color="red"> <B><U>1. The LTRIM() and RTRIM() functions:</U></B><br> <font face="courier" size=2 color="black"> <br> Suppose somebody mistakingly inserted the (2,'&nbsp Carlson;) row into table B. The mistake then is the white space<br> on the left of the name 'Carlson'. If you want to Left trim, or Right trim spaces from character fields (or variables), you<br> can use the LTRIM and RTRIM functions.<br> Function LTRIM: Removing leading spaces.<br> Function RTRIM, Removing trailing spaces.<br> <br> <B>Example 1:</B><br> <br> Just compare the resultsets from the two queries below:<br> <br> <font face="courier" size=2 color="blue"> SELECT name FROM B<br> <br> 123:BB:Johnson<br> &nbsp Carlson<br> I want a lot of gold in my bathroom<br> <br> SELECT LTRIM(name) FROM B<br> <br> 123:BB:Johnson<br> Carlson<br> I want a lot of gold in my bathroom<br> <font face="courier" size=2 color="black"> <br> <B>Example 2:</B><br> <br> Now let's demonstrate LTRIM and RTRIM using a piece of TSQL code:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @varwithspaces varchar(100)<br> DECLARE @varwithoutspaces varchar(100)<br> <br> SELECT @varwithspaces=' the_text '<br> SELECT @varwithoutspaces=LTRIM(RTRIM(@varwithspaces))<br> <br> -- Now show both variables:<br> <br> SELECT @varwithspaces<br> SELECT @varwithoutspaces<br> <br> <font face="courier" size=2 color="black"> If you execute above code, you should see this:<br> <br> &nbsp the_text <br> (1 row(s) affected)<br> <br> the_text <br> (1 row(s) affected)<br> <br> <br> <font face="courier" size=2 color="red"> <B><U>2. The substring() function:</U></B><br> <font face="courier" size=2 color="black"> <br> The substring() function has the following syntax:<br> <br> SUBSTRING(string, startposition, length)<br> <br> So you can use it if you want to "clip" a field or variable, that is, you want only a piece of the text,<br> starting from position "startposition" with a lenght of "length".<br> <br> <B>Example 1.</B><br> <br> Take a look at the following piece of TSQL:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @bigvar varchar(100)<br> DECLARE @smallvar varchar(100)<br> <br> SELECT @bigvar='12345678910abcdefghi'<br> SELECT @smallvar=substring(@bigvar,10,5)<br> <br> -- Now show both variables:<br> <br> SELECT @bigvar<br> SELECT @smallvar<br> <br> <font face="courier" size=2 color="black"> If you would execute the above code fragment, you should see this for output:<br> <br> 12345678910abcdefghi<br> <br> (1 row(s) affected)<br> <br> 10abc<br> <br> (1 row(s) affected)<br> <br> Note how the "10abc" piece of text, is exactly the original string <U>as of</U> position 10 with a length of 5 characters.<br> <br> <B>Example 2.</B><br> <br> The example below, should speak for itself.<br> <br> <font face="courier" size=2 color="blue"> <br> CREATE TABLE TABLONG<br> (<br> mycolumn VARCHAR(128)<br> )<br> <br> CREATE TABLE TABSHORT<br> (<br> mycolumn VARCHAR(128)<br> )<br> <br> INSERT INTO TABLONG VALUES ('00123-CODE:09876')<br> INSERT INTO TABLONG VALUES ('00123-CODE:09877')<br> <br> SELECT * FROM TABLONG<br> <br> mycolumn<br> ------------------------<br> 00123-CODE:09876<br> 00123-CODE:09877<br> <br> (2 row(s) affected)<br> <br> INSERT INTO TABSHORT<br> SELECT SUBSTRING(mycolumn,12,5)<br> FROM TABLONG<br> <br> Notice that the rows in table TABSHORT are the clipped data from table TABLONG.<br> <br> Note: in case you did not know, it's indeed possible to INSERT rows into a table, using a SELECT from another table.<br> <br> SELECT * FROM TABSHORT<br> <br> mycolumn<br> -------------------------------<br> 09876<br> 09877<br> <br> (2 row(s) affected)<br> <br> <font face="courier" size=2 color="black"> <br> <font face="courier" size=2 color="red"> <B><U>3. The LEN() function:</U></B><br> <font face="courier" size=2 color="black"> <br> This one is very easy. It returns the length of a string, or field of a table.<br> <br> <B>Example 1:</B><br> <br> If you had created table "B" as was shown in the beginning of this section, then take a look at this:<br> <br> SELECT LEN(NAME) FROM B<br> <br> -----------<br> 14<br> 10<br> 35<br> <br> (3 row(s) affected)<br> <br> Note how LEN() just returns how many characters a field or variable has. Note that also spaces are counted.<br> So, LEN() is very usable to determine the length of a field or variable.<br> In other sections, we will find very good use of the LEN() function, I assure you!<br> <br> <B>Example 2:</B><br> <br> Take a look at this TSQL:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @MyVar varchar(100)<br> SELECT @MyVar='This is some text'<br> <br> SELECT @MyVar<br> SELECT LEN(@MyVar)<br> <br> <font face="courier" size=2 color="black"> If you run that code, you should see:<br> <br> This is some text<br> <br> (1 row(s) affected)<br> <br> 17<br> <br> (1 row(s) affected)<br> <br> <br> <font face="courier" size=2 color="red"> <B><U>4. The REPLACE() function:</U></B><br> <font face="courier" size=2 color="black"> <br> This is a great function, to replace existing text in a field of a table (or variable),<br> by some other text.<br> <br> Syntax:<br> <br> REPLACE(field, 'string_to_be_replaced', 'replacement')<br> <br> <B>Example 1:</B><br> <br> <font face="courier" size=2 color="blue"> DECLARE @MyString VARCHAR(128)<br> DECLARE @MyOtherString VARCHAR(128)<br> <br> SET @MyString='The great Mr. A'<br> SET @MyOtherString=REPLACE(@MyString,'Mr','Mrs')<br> SELECT @MyOtherString<br> <br> ------------------<br> The great Mrs. A<br> <br> <font face="courier" size=2 color="black"> (1 row(s) affected)<br> <br> <B>Example 2:</B><br> <br> Suppose you have this data in the INVENTORY table:<br> <br> id &nbsp Article &nbsp Manufacturer<br> 5 &nbsp bycicle &nbsp ABC Corp<br> 6 &nbsp Hammer &nbsp XYZ Corp<br> 7 &nbsp MotorOil &nbsp XYZ Corp<br> 8 &nbsp Roadmap &nbsp EFG Corp<br> etc..<br> <br> Now, suppose we need to replace the XYZ in "XYZ Corp" data, with DEVO:<br> <br> <font face="courier" size=2 color="blue"> UPDATE INVENTORY<br> SET manufacturer=REPLACE(manufacturer, 'XYZ','DEVO')<br> <br> <font face="courier" size=2 color="black"> Then the manufacturer field, only where the data contained 'XYZ', will be changed to contain 'DEVO'.<br> <br> id &nbsp Article &nbsp Manufacturer<br> 5 &nbsp bycicle &nbsp ABC Corp<br> 6 &nbsp Hammer &nbsp DEVO Corp<br> 7 &nbsp MotorOil &nbsp DEVO Corp<br> 8 &nbsp Roadmap &nbsp EFG Corp<br> etc..<br> <br> <br> <br> <font face="courier" size=2 color="red"> <B><U>5. The PATINDEX() function:</U></B><br> <font face="courier" size=2 color="black"> <br> In the next section (section 1.13), we will see a great example of the usuability of this function.<br> <br> Syntax:<br> PATINDEX("pattern", field or variable)<br> <br> Essentially, the PATINDEX() function works like this.<br> If you want to find the <B>starting position</B> of where a string of a certain "pattern" starts from<br> in a column of a table, or any expression, you can USE the PATINDEX("pattern", column) function.<br> <br> An example will illustrate this.<br> <br> <B>Example 1:</B><br> <br> In the beginning of this section, we created table "B", and here is it's content:<br> <br> SELECT * FROM B<br> <br> Resultset:<br> <br> id &nbspname<br> 1 &nbsp 123:BB:Johnson<br> 2 &nbsp &nbsp Carlson<br> 3 &nbsp I want a lot of gold in my bathroom<br> <br> Now suppose I want to know where the "pattern" "gold" starts from in record no 3,<br> we can use this:<br> <br> <font face="courier" size=2 color="blue"> SELECT PATINDEX('%gold%',name)<br> FROM B WHERE id=3<br> <font face="courier" size=2 color="black"> <br> If you run that statement, you would find that the pattern %gold% starts from position 17.<br> So, the PATINDEX() function returns an integer.<br> See the next section for a useable example.<br> <br> <br> <font face="courier" size=2 color="red"> <B><U>6. The CHARINDEX() function:</U></B><br> <font face="courier" size=2 color="black"> <br> This function returns the starting position of the "expression1" in a character string (expression2).<br> So, it works remarkably the same as the function PATINDEX(). There are a few minor differences though.<br> PATINDEX can use wildcard characters, but CHARINDEX cannot.<br> <br> Syntax: <B>CHARINDEX ( expression1 , expression2 [ , start_location ] ) </B><br> <br> If "expression1" in "expression2" is not found, the function returns "0".<br> <br> Here are a few simple examples:<br> <br> <font face="courier" size=2 color="blue"> SELECT CHARINDEX('/','abcd/efgh')<br> <br> Will return "5".<br> <br> SELECT CHARINDEX('John','This morning John went to work, but he really disliked it')<br> <br> Will return "14".<br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section13">1.13. REMOVING "UNWANTED" CHARACTERS FROM A FIELD OR STRING:</h3><br> <font face="courier" size=2 color="black"> Suppose you have a variable, containing "unwanted" characters like "\", or "%" etc..<br> and suppose you need to "clean" that, so that only <B>ordinary characters</B> like a-z, A-Z, and 0-9 remain,<br> the following script might be handy.<br> To appreciate what the script does, copy/paste it in a Query Window and execute it.<br> <br> <font face="courier" size=2 color="blue"> DECLARE @s varchar(100)<br> DECLARE @i INT<br> <br> SELECT @s = 'asd i/.,<>as>[{}]vnbv' -- assign a value to variable @s, so before cleaning<br> SELECT @s -- show @s as it is now<br> <br> SELECT @i = patindex('%[^a-z^A-Z^0-9^ ]%', @s)<br> &nbsp while @i > 0<br> &nbsp BEGIN<br> &nbsp &nbsp SELECT @s = replace(@s, substring(@s, @i, 1), '')<br> &nbsp &nbsp SELECT @i = patindex('%[^a-z^A-Z^0-9^ ]%', @s)<br> &nbsp END<br> <br> SELECT @s -- show @s as it is after "cleaning<br> <br> <font face="courier" size=2 color="black"> <br> Then these would be the values of @s before and after the cleaning:<br> <br> asd i/.,<>as>[{}]vnbv<br> asd iasvnbv<br> <br> Ofcourse, we can apply that to fields of a table as well.<br> Just take a look at this example:<br> <br> <font face="courier" size=2 color="blue"> <br> CREATE TABLE Z<br> (<br> id INT,<br> name VARCHAR(20))<br> <br> <font face="courier" size=2 color="black"> Having just one testrecord in Z, is quite enough for our demo, so let's insert this one:<br> <br> <font face="courier" size=2 color="blue"> INSERT INTO Z VALUES (1,'abc/vv>()&%aaa')<br> <br> SELECT * FROM Z<br> <br> Resultset:<br> <br> id name<br> 1 abc/vv>()&%aaa<br> <br> <font face="courier" size=2 color="black"> Now lets run the following script:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @s varchar(100)<br> DECLARE @i INT<br> <br> SELECT @s = (SELECT NAME FROM Z WHERE ID=1)-- assign a value to variable @s<br> SELECT @s -- show @s as it is now<br> <br> SELECT @i = patindex('%[^a-z^A-Z^0-9^ ]%', @s)<br> &nbsp while @i > 0<br> &nbsp BEGIN<br> &nbsp &nbsp SELECT @s = replace(@s, substring(@s, @i, 1), '')<br> &nbsp &nbsp SELECT @i = patindex('%[^a-z^A-Z^0-9^ ]%', @s)<br> &nbsp END<br> <br> SELECT @s -- show @s as after "cleaning<br> <br> <font face="courier" size=2 color="black"> The two "SELECT @s" in the above script, will produce:<br> <br> abc/vv>()&%aaa -- the first select @s<br> abcvvaaa -- the second select @s<br> <br> So, we succeeded in removing the unwanted characters. The main mechanism for this to work, is the<br> PATINDEX() function, which we have seen (among other functions) in the former section.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section14">1.14. REMOVING "UNWANTED" QUOTES FROM A FIELD OR STRING:</h3><br> <font face="courier" size=2 color="black"> Suppose you have a table with rows like:<br> <br> name city<br> 'Harry' 'Boston'<br> 'Miriam' 'Seattle'<br> etc..<br> <br> where all fields are enclosed by ''.<br> To create a new table without these quotes around the data, you can use the example below.<br> Maybe you can create a more "elegant" or more "efficient" piece of TSQL code.<br> Actually, the script below is not really good enough to run on any table of value.<br> It only demonstrates the use of the LEN() function, as was discussed in the former section.<br> <br> <B>Example:</B><br> <br> Let's create the table (TAB1) first and insert some rows with quotes around the data.<br> Then we create a table (TAB2) with the same structure as TAB1, which will hold the "cleaned" rows after the script has run.<br> <br> <br> CREATE TABLE TAB1 -- name the table TAB1 if you want to follow below script<br> (<br> name varchar(32),<br> city varchar(32)<br> )<br> <br> <br> CREATE TABLE TAB2 -- name the table TAB2 if you want to follow below script<br> (<br> name varchar(32),<br> city varchar(32)<br> )<br> <br> INSERT INTO TAB1 VALUES ('''Harry''', '''Boston''')<br> INSERT INTO TAB1 VALUES ('''Miriam''', '''Seattle''')<br> <br> Note the use of '''text''', in order to insert text data surrounded with quotes, into the table.<br> <br> Let's see what's in table TAB1:<br> <br> name &nbsp city<br> 'Harry' &nbsp 'Boston'<br> 'Miriam' &nbsp 'Seattle'<br> <br> Now, let's fill table "tab2" using the following script.<br> <br> <font face="courier" size=2 color="blue"> DECLARE @name varchar(32)<br> DECLARE @city varchar(32)<br> DECLARE @length_name INT<br> DECLARE @length_city INT<br> DECLARE @name2 varchar(32)<br> DECLARE @city2 varchar(32)<br> <br> DECLARE cur1 CURSOR FOR<br> SELECT name, city FROM tab1<br> <br> OPEN cur1<br> FETCH NEXT FROM cur1 INTO @name, @city<br> <br> WHILE (@@fetch_status<>-1)<br> BEGIN<br> <br> SELECT @length_name =LEN(@name)<br> SELECT @length_city =LEN(@city)<br> <br> SELECT @name2 =substring(@name,2,@length_name-2)<br> SELECT @city2 =substring(@city,2,@length_city-2)<br> <br> INSERT INTO TAB2<br> values<br> (@name2,@city2)<br> <br> FETCH NEXT FROM cur1 INTO @name, @city<br> <br> END<br> <br> CLOSE cur1<br> DEALLOCATE cur1<br> <font face="courier" size=2 color="black"> <br> Note:<br> <br> Since we are busy with quotes anyway, suppose you need to assign a value to a (string) variable,<br> where the value <I>includes a quote</I>, you can do it like so:<br> <br> DECLARE @VAR1 VARCHAR(64)<br> SET @VAR1='Appie''s'<br> SELECT @var1 -- this statement shows the value of @VAR1 <br> <br> You should see the result:<br> <br> Appie's<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section15">1.15. SIMPLE OVERVIEW MOST IMPORTANT SQL SERVER DATATYPES:</h3><br> <font face="courier" size=2 color="black"> SQL Server uses "strong typing". In this case it means that suppose a column of a table is of datatype "datetime",<br> then you cannot insert just any number into that field. Likewise, if a column is of datatype "integer", you cannot<br> just put a number with decimals into that field.<br> Sometimes you must be able to "convert" a variable or field of a certain datatype, into another datatype.<br> <br> SQL Server allows you to use a large number of different <I>datatypes</I>.<br> We all know the "administrative like" datatypes like integer, decimal(n,m), datetime, varchar(n) and char(n),<br> which are often used in many administrative databases (administration, bookkeeping, orderentry, logistics etc..).<br> But there are lots more, like the XML datatype, which is native to store XML, or varbinary which you can use<br> to store any binary (like docs etc..)<br> <br> Here are the most often used datatypes, with a short description:<br> <br> ________________________________________________________________________________________________<br> <B>character based data:</B><br> ________________________________________________________________________________________________<br> char(length): A fixed length character datatype. For example, "char(4)" will always reserve 4 bytes of space.<br> ________________________________________________________________________________________________<br> nchar(length): just like char, but this time UNICODE<br> ________________________________________________________________________________________________<br> varchar(length): A variable length character datatype. It will only store what's needed, up to the specified length.<br> ________________________________________________________________________________________________<br> nvarchar(length): just like varchar, but this time UNICODE<br> ________________________________________________________________________________________________<br> text: if you need to store a lot of text. Max 1G characters.<br> ________________________________________________________________________________________________<br> ntext: just like text, but now in UNICODE<br> ________________________________________________________________________________________________<br> <B>nummeric based data:</B><br> ________________________________________________________________________________________________<br> decimal(n,m): decimal notation/type, meaning n digits before the "," or "." seperator, and m precision after.<br> ________________________________________________________________________________________________<br> nummeric(n,m): same as decimal(n.m)<br> ________________________________________________________________________________________________<br> float(n): can be used for large floating point numbers<br> ________________________________________________________________________________________________<br> bigint: whole numbers=integers: -2^63 to 2^63; use 8 bytes storage<br> ________________________________________________________________________________________________<br> int: whole number=integers: -2^31 to 2^31; uses 4 bytes storage<br> ________________________________________________________________________________________________<br> smallint: whole number=integers: -32768 to 32767; uses 2 bytes storage<br> ________________________________________________________________________________________________<br> tinyint: whole numbers=integers 0 to 255; uses 1 byte storage<br> ________________________________________________________________________________________________<br> <B>date/time datatype:</B><br> ________________________________________________________________________________________________<br> datetime: stores date + time (accuracy 0.00333 s); 8 bytes; 01/01/1753 - 31/12/9999<br> ________________________________________________________________________________________________<br> datetime2: stores date + time (accuracy 100 ns); 6 to 8 bytes; 01/01/0001 - 31/12/9999<br> ________________________________________________________________________________________________<br> smalldatetime: like above, but a "smaller" variant; 4 bytes; 01/01/1900 - 06/06/2079<br> ________________________________________________________________________________________________<br> date: stores just plain dates like "12/12/2010"; 3 bytes<br> ________________________________________________________________________________________________<br> time: stores plain time, with accuracy of 100 ns; 3 to 5 bytes<br> ________________________________________________________________________________________________<br> <B>binary datatypes:</B><br> ________________________________________________________________________________________________<br> image: e.g.: to store pdf, word files, images, or other binary data; up to 2G<br> ________________________________________________________________________________________________<br> varbinary(max): e.g.: to store pdf, word files, images, or other binary data; up to 2G<br> ________________________________________________________________________________________________<br> binary: Fixed-length binary data with a length of n bytes, where n from 1 through 8,000. storage is n bytes.<br> ________________________________________________________________________________________________<br> <B>XML datatype:</B><br> ________________________________________________________________________________________________<br> XML: "native" datatype to store XML documents or fragments<br> ________________________________________________________________________________________________<br> <B>Spatial Datatype:</B><br> ________________________________________________________________________________________________<br> geometry: supports geometric data (points, lines, and polygons) <br> ________________________________________________________________________________________________<br> geography: a Euclidean coordinate system. This type represents geographic objects on an area on the Earth's surface<br> ________________________________________________________________________________________________<br> <B>uniqueidentifier:</B><br> ________________________________________________________________________________________________<br> uniqueidentifier: Globally Unique ID, which is "supposed" to uniquely determine a row, between entities, instances, and objects.<br> ________________________________________________________________________________________________<br> <br> <br> <B>Choose your datatype carefully:</B><br> <br> If you choose "too wide" datatypes, you fill a database block "too quickly". This means that a smaller number of rows<br> fits in a table page.<br> This cost performance, because the database needs to read more pages, to get the same information.<br> For example, suppose some table has a "COMMENT" column. Think of the consequence if some developer choose a<br> a datatype of "char(2000)", meaning a fixed column lenght of 2000 bytes.<br> Now if the comment is at most just a few words, then that's a true waste.<br> <br> As another example: If you are sure you can use a smallint, then don't take a bigint.<br> Carefully choosing datatypes, not only saves space, but will also lower computing power as well.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section16">1.16. THE CAST AND CONVERT CONVERSION FUNCTIONS:</h3><br> <font face="courier" size=2 color="black"> Sometimes you must be able to "convert" a variable, or field, of a certain datatype, into another datatype.<br> Ofcourse, some datatypes are very similar (like char, varchar). For those, SQL Server will use "implicit conversion"<br> at certain events.<br> For example, if you have a table with char columns, and you insert those rows to another (similar) table with varchar columns,<br> then that will work (provided that the varchar columns are not too small to hold the char values).<br> But at other times, you really need to "convert" values from one type to another.<br> <br> For this, the CAST() and CONVERT() conversion functions can be used. CAST() and CONVERT() are almost equal in functionality.<br> You can say that CONVERT() offers somewhat more options to pick from.<br> We are going to illustrate the use of CONVERT and CAST with a couple of examples.<br> <br> The syntax for CAST() is:<br> CAST ( expression AS data_type [ (length ) ])<br> <br> The syntax for CONVERT is:<br> CONVERT ( data_type [ ( length ) ] , expression [ , style ] )<br> <br> The syntax might look "difficult", which it is not.<br> The "expression" is a variable, or field" that you want to convert.<br> So let's take a look at a few examples:<br> <br> <B>Example 1</B><br> <br> In this example, we will use one of SQL Server "internal" functions. The function GETDATE(), just gets the current date/time,<br> and we can use it to assign a value to a variable of datatype "datetime".<br> <br> <font face="courier" size=2 color="blue"> <br> DECLARE @orderdate DATETIME<br> DECLARE @orderdate_as_char VARCHAR(30)<br> <br> SELECT @orderdate=GETDATE() -- now @orderdate will be assigned a value<br> <br> SELECT @orderdate_as_char=<B>CAST(@orderdate AS varchar(30))</B><br> <br> -- Let's print the values of the variables:<br> <br> SELECT @orderdate<br> SELECT @orderdate_as_char<br> <br> <font face="courier" size=2 color="black"> If you run the above code, you will see something like the following output.<br> Your specific output may differ somewhat, depending on the "collation" of your system.<br> <br> 2011-05-07 16:28:37.630<br> <br> (1 row(s) affected)<br> <br> May 7 2011 4:28PM<br> <br> (1 row(s) affected)<br> <br> Exercise: run the same code, but this time use "CAST(@orderdate AS varchar(10))", so now we convert<br> to a varchar of length 10. What do you see?<br> <br> Notice that the syntax of CAST (as shown above), actually is no more than:<br> CAST ( variable_or_field AS new_data_type [ (length ) ])<br> <br> <B>Example 2</B><br> <br> <font face="courier" size=2 color="blue"> DECLARE @l_rowcount INT<br> SET @l_rowcount=30 -- Also SET can be used to assign a value to a variable<br> <br> PRINT 'NUMBER OF ROWS PROCESSED: '+@l_rowcount<br> <br> <font face="courier" size=2 color="black"> If we run that code, we get:<br> <br> <font face="courier" size=2 color="red"> Msg 245, Level 16, State 1, Line 4<br> Conversion failed when converting the varchar value 'NUMBER OF ROWS PROCESSED: ' to data type int.<br> <br> <font face="courier" size=2 color="black"> So, the PRINT statement, to print to your screen, expects concatenations (using "+") to be of the same type.<br> That is,<br> <br> PRINT 'some string'+@string_variable<br> <br> is OK. <br> So let's change the third line in above code into:<br> <br> <font face="courier" size=2 color="blue"> PRINT 'NUMBER OF ROWS PROCESSED: '+CAST(@l_rowcount AS varchar)<br> <br> <font face="courier" size=2 color="black"> and now all is well!<br> <br> <B>Example 3</B><br> <br> The CONVERT() function works exactly the same as CAST(). Only, if you take a look at both syntaxes as shown above,<br> you can use additionally (and optionally) in CONVERT(), a "style" clause, which determines the formatting.<br> Let's take a look at an example.<br> <br> <font face="courier" size=2 color="blue"> DECLARE @orderdate DATETIME<br> DECLARE @orderdate_as_char VARCHAR(30)<br> <br> SELECT @orderdate=GETDATE() -- now @orderdate will be assigned a value, which is the current date/time<br> <br> -- Now let's try different "styles" and see what we get<br> <br> SELECT CONVERT(varchar(30), @orderdate) -- no style selected<br> SELECT CONVERT(varchar(30), @orderdate, 113)<br> SELECT CONVERT(varchar(30), @orderdate, 111)<br> SELECT CONVERT(varchar(30), @orderdate, 102)<br> SELECT CONVERT(varchar(30), @orderdate, 20)<br> SELECT CONVERT(varchar(30), @orderdate, 10)<br> SELECT CONVERT(varchar(10), @orderdate, 20)<br> <br> <font face="courier" size=2 color="black"> Try to run the above code. You should see various representations.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section17">1.17. A FEW WORDS ON GRANTING ROLES AND PERMISSIONS TO LOGINS:</h3><br> <font face="courier" size=2 color="black"> <font face="arial" size=2 color="black"> This is not a section where you get a formal presentation on how to deal with security and permissions in SQL Server.<br> SQL Server permissions can be granted on server, database, schema, and database object level. <br> <br> Instead, here only a quick practical intro is presented on <I>how to work</I> with roles<br> and how you can grant permissions to users and roles. Also we will show how to script the memberlist of roles.<br> <br> <font face="courier" size=2 color="black"> There are occasions where working with the graphical SSMS is really easy, like granting a role to a login.<br> But suppose you want a lot of logins to grant a certain role or permission (e.g.: to a table).<br> Then scripting is the way to go (this is not to say that the graphical studio does not provide you lots of tools as well).<br> <br> There are two "main" ways here:<br> <br> <ol> <li> You can grant a "role" to a login. The role is comparable to a group, with a predefined set of permissions. Using TSQL, you will use the specialized system stored procedures to add a user to a role.</li> <li> You can grant a certain "permission" to a login (like select, or execute), on a certain object (like a table, stored procedure). Using TSQL, you will use the GRANT statement.</li> </ol> <br> <br> In SQL Server documentation, the "talk" is like this:<br> <br> You GRANT a role or permission <B><U>TO</U></B> a "principal". It's an expensive name for anything<br> that can access the Server. So, the common principal we can think of, is a login.<br> <br> You GRANT a role or permisson <B><U>ON</U></B> a "securable", which is an object<br> like a Table or View.<br> <br> Only one thing is very important here:<br> <br> <B> - You grant a <U>permission</U> (like SELECT), TO a login, ON an object (like a Table), using the GRANT statement.</B><br> <br> So, if you are already at "home" with for eaxmple Oracle, GRANTING a permission works exactly the same<br> in SQL Server, like for example:<br> <br> <font face="courier" size=2 color="blue"> GRANT SELECT ON hr.EMPLOYEES TO harry<br> <br> <font face="courier" size=2 color="black"> <B>- Granting a user a <U>role</U> (or adding a user to a role) has to be done using a system stored procedure.</B><br> <br> But, in most databases, like Oracle, you use the GRANT statement as well when you grant a role.<br> In SQL Server it is a bit different: here you need to use a few specific system stored procedures,<br> like for example:<br> <br> <font face="courier" size=2 color="blue"> EXEC sp_addrolemember 'db_datareader', 'mary'<br> <font face="courier" size=2 color="black"> <br> <br> Let's take a look at roles first.<br>. <br> <font face="courier" size=2 color="red"> <B><U>17.1 Roles:</B></U><br> <font face="courier" size=2 color="black"> <br> There are actually three types of roles:<br> <br> - database roles: every database has a number of roles with predefined permissions.<br> - server roles: every server/instance has one set of server roles with predefined permissions.<br> - application roles: enables an application to run with its own, user-like privileges.<br> <br> <U>- Database roles:</U><br> <br> Every database under your Instance, has the same set of database roles.<br> Each role has a certain predefined set of permissions.<br> For example, the "db_datareader" role has the permission to SELECT any table of the database.<br> You probably agree that's not too hard to derive from the "name" of the role, what it's purpose is.<br> The following database roles are present in any database:<br> <br> ________________________________________________________________________________________________<br> db_datareader: members can select any user table<br> ________________________________________________________________________________________________<br> db_datawriter: members can select, insert, update, delete statements<br> ________________________________________________________________________________________________<br> db_accessadmin: members can control access to the database<br> ________________________________________________________________________________________________<br> db_backupoperator: members can backup the database<br> ________________________________________________________________________________________________<br> db_ddladmin: members can issue ddl (create and modify objects) c<br> ________________________________________________________________________________________________<br> db_denydatareader: members cannot read any data in the user tables within a database <br> ________________________________________________________________________________________________<br> db_denydatawriter: members cannot modify any data in the user tables within a database <br> ________________________________________________________________________________________________<br> db_owner: members have all permissions<br> ________________________________________________________________________________________________<br> db_securityadmin: members control permissions on securables<br> ________________________________________________________________________________________________<br> public: role where all users are member of. Should have the least possible permissions<br> ________________________________________________________________________________________________<br> <br> - To assign a user a database role (grant a role) use the "sp_addrolemember" stored procedure, like for example:<br> <br> <font face="courier" size=2 color="blue"> USE Your_database_name<br> GO<br> <br> EXEC sp_addrolemember 'db_datareader', 'mary'<br> GO<br> <br> <font face="courier" size=2 color="black"> - To remove a user from a database role, use:<br> <br> <font face="courier" size=2 color="blue"> USE Your_database_name<br> GO<br> <br> EXEC sp_droprolemember 'db_datareader', 'mary'<br> <br> <font face="courier" size=2 color="black"> It is possible to create a database role, if you feel that the existing roles do not conform to your<br> security policies. You can create a database role, then grant the neccessary permissions to it, and<br> grant the role to users.<br> You can create a database role like so:<br> <br> <font face="courier" size=2 color="blue"> USE Your_database_name<br> GO<br> <br> CREATE ROLE role_name<br> GO<br> <br> <font face="courier" size=2 color="black"> <U>- Server roles:</U><br> <br> These are "server wide" roles. So there is only one set of "server roles" per Instance.<br> Regular database users should not be member of these roles. They are usually meant for users<br> with dba and admin duties.<br> <br> The following Server roles are present for your Server:<br> <br> ________________________________________________________________________________________________<br> bulkadmin: Granted: ADMINISTER BULK OPERATIONS<br> ________________________________________________________________________________________________<br> dbcreator: Granted: CREATE DATABASE<br> ________________________________________________________________________________________________<br> diskadmin: Granted: ALTER RESOURCES<br> ________________________________________________________________________________________________<br> processadmin: Granted: ALTER ANY CONNECTION, ALTER SERVER STATE<br> ________________________________________________________________________________________________<br> securityadmin: Granted: ALTER ANY LOGIN<br> ________________________________________________________________________________________________<br> serveradmin: Granted: ALTER ANY ENDPOINT, ALTER RESOURCES, ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN, VIEW SERVER STATE <br> ________________________________________________________________________________________________<br> setupadmin: Granted: ALTER ANY LINKED SERVER <br> ________________________________________________________________________________________________<br> sysadmin: (has full power) Granted with GRANT option: CONTROL SERVER<br> ________________________________________________________________________________________________<br> <br> - To assign a user Server role, use the "sp_addsrvrolemember " stored procedure, like for example:<br> <br> <font face="courier" size=2 color="blue"> EXEC sp_addsrvrolemember 'mary', 'sysadmin'<br> GO<br> <br> <font face="courier" size=2 color="black"> - To remove a user from a server role, use the "sp_dropsrvrolemember " stored procedure, like for example:<br> <br> <font face="courier" size=2 color="blue"> EXEC sp_dropsrvrolemember 'mary', 'sysadmin'<br> <br> <font face="courier" size=2 color="black"> <br> <font face="courier" size=2 color="red"> <B><U>17.2 Granting Permissions:</B></U><br> <font face="courier" size=2 color="black"> <br> The database roles 'db_datareader" and "db_datawriter", might have a too wide range of permissions.<br> <br> What is possible, is that you GRANT permissions to a user on a selection of tables and other objects.<br> What is even better, is that you create a role, grant the neccessary permissions to that role, and as<br> the last step, grant the role to users.<br> This is better, since you then do not grant permissions to (possibly a large) number of users.<br> You only need to add and remove users to that role, as neccessary.<br> <br> Anyway, you need to know how to grant individual permissions on objects (securables) to principals (users, roles), using the GRANT statement.<br> <br> There are very many classes on which the GRANT statement can operate.<br> The most import ones are:<br> <br> DML Data Manipulation, or "Object Level Rights", like granting select, insert, update, delete, execute permissions.<br> DDL Data Definition: like granting CREATE object, DROP object permissions.<br> <br> Here are a few representative examples:<br> <br> <font face="courier" size=2 color="blue"> GRANT SELECT on CONTACTS to harry<br> <br> GRANT SELECT, INSERT on EMPLOYEE to harry<br> <br> GRANT SELECT, INSERT, UPDATE on ORDERS to SALESUSERS -- a database role you created<br> <br> GRANT EXECUTE on stp_InsertEmployee to SALESUSERS<br> <br> <font face="courier" size=2 color="black"> <br> <br> <font face="courier" size=2 color="red"> <B><U>17.3 Retrieving lists:</B></U><br> <font face="courier" size=2 color="black"> <br> If you have a large database, with many users and roles, it can be difficult to get a good overview<br> of which user is member of which database role.<br> The same might be true for Server roles.<br> <br> Let's see if we can build scripts that will create listings.<br> <br> Before we do so, I like to attend you on a specific system view, namelely "sys.all_objects".<br> This view indeed has information on almost all objects, so on other "system views" as well !<br> The columns in this view, which I think are of interest, are "name" and "type_desc".<br> So, if you want to know which dictionary system view has information on "principals", or "table" etc..,<br> you might try queries like:<br> <br> <font face="courier" size=2 color="blue"> select name, type_desc from sys.all_objects where name like '%princip%'<br> <br> select name, type_desc from sys.all_objects where name like '%role%'<br> <br> select name, type_desc from sys.all_objects where name like '%user%'<br> etc..<br> <br> <font face="courier" size=2 color="black"> If the type description is a "view", you can easily query it !<br> <br> <font face="courier" size=2 color="black"> <br> <br> <B>17.3.1 Getting a list of database role members:</B><br> <br> script 1: Usable for 2005/2008. Actually, this is just a query:<br> <br> <font face="courier" size=2 color="blue"> select user_name(role_principal_id) AS ROLE, user_name(member_principal_id) AS USERNAME <br> from sys.database_role_members <br> <br> <br> <font face="courier" size=2 color="black"> script 2: usable for 2000/2005/2008<br> <br> <font face="courier" size=2 color="blue"> -- it's probably best to set the "result to text" option in the query menu<br> <br> DECLARE @dbname VARCHAR(64)<br> <br> DECLARE cur1 CURSOR FOR<br> SELECT name<br> FROM master.dbo.sysdatabases<br> <br> OPEN cur1<br> FETCH NEXT FROM cur1 INTO @dbname<br> <br> WHILE (@@fetch_status<>-1)<br> BEGIN<br> PRINT 'DATABASE ROLES AND MEMBERS FOR: ' +@dbname<br> PRINT ' '<br> <br> EXEC('SELECT <br> DbRole = substring(g.name, 1, 30), <br> MemberName = substring(u.name, 1, 30)<br> FROM '+@dbname+'.dbo.sysUSErs'+' u'+','<br> +@dbname+'.dbo.sysUSErs'+' g'+','<br> +@dbname+'.dbo.sysmembers'+' m '<br> +'WHERE g.uid = m.groupuid<br> AND g.issqlrole = 1<br> AND u.uid = m.memberuid<br> ORDER BY dbrole')<br> <br> FETCH NEXT FROM cur1 INTO @dbname<br> END<br> <br> CLOSE cur1<br> DEALLOCATE cur1<br> <br> <br> <font face="courier" size=2 color="black"> <B>17.3.2 Getting a list of Server role members:</B><br> <br> <font face="courier" size=2 color="blue"> SELECT <br> rolename = rolep.name, <br> membername = memp.name <br> FROM <br> sys.server_role_members rm <br> JOIN sys.server_principals rolep ON rm.role_principal_id = rolep.principal_id <br> JOIN sys.server_principals memp ON rm.member_principal_id = memp.principal_id <br> <br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section18">1.18. A FEW WORDS ON SHOWING PERMISSIONS USING TSQL:</h3><br> <font face="courier" size=2 color="red"> Note: to get a list of members of "database roles" or "Server roles", please see section 1.17.3.<br> <br> <font face="courier" size=2 color="black"> <br> In this section, we want to create lists of users and their granted permissions.<br> You should realize, that usually a "user" obtains it's rights due to membership in roles.<br> Anyway, that's the "clean" way to grant permissions.<br> But, it's not uncommon that explicitly permissions are granted to individual logins (user).<br> <br> Before we create a few scripts that list user rights, we first use a function that will show you the possible permissions<br> that can be granted on the Server level, database level, and object level.<br> <br> Again, you can query "sys.all_objects" to try to find system views or functions,<br> that you can use to query for security related information.<br> <br> <font face="courier" size=2 color="blue"> select name, type_desc From sys.all_objects where name like '%permiss%'<br> <font face="courier" size=2 color="black"> <br> From this, the function "fn_my_permissions()" seems promising.<br> <br> <br> <font face="courier" size=2 color="red"> <B><U>17.1 The fn_my_permissions() function.</U></B><br> <font face="courier" size=2 color="black"> <br> The below function applies to SQL Server 2005/2008.<br> <br> <font face="courier" size=2 color="blue"> -- Server Level:<br> select * from fn_my_permissions(NULL, NULL)<br> <br> -- Database Level:<br> select * from fn_my_permissions(NULL, 'database')<br> <br> -- Object level, like the object dbo.EMPLOYEE:<br> select * from fn_my_permissions('dbo.EMPLOYEE', 'object')<br> <br> <font face="courier" size=2 color="black"> Not only a list of possible permissions can be shown, but you can let the function apply to a user as well:<br> <br> <font face="courier" size=2 color="blue"> execute ('select * from fn_my_permissions(NULL, ''database'')') AS USER = 'harry'<br> <br> <font face="courier" size=2 color="black"> The above statement should show you what Database Level permissions harry has.<br> <br> <font face="courier" size=2 color="blue"> execute ('select * from fn_my_permissions(''dbo.EMPLOYEE'', ''object'')') AS USER = 'harry'<br> <br> <font face="courier" size=2 color="black"> The above statement should show you permissions harry has on the table EMPLOYEE.<br> <br> <br> <font face="courier" size=2 color="red"> <B><U>17.2 Creating a list of object permissions.</U></B><br> <font face="courier" size=2 color="black"> <br> <U>(1). An attemt to list the explicitly granted rights to users:</U><br> <br> First of all, you might take a look on what you can find in the "INFORMATION_SCHEMA.TABLES" view:<br> <br> select * from INFORMATION_SCHEMA.TABLES<br> <br> Evidently, here you can find all table names in your database.<br> <br> Now, just use the system stored procedure "sp_helprotect" on a table name, like for example:<br> <br> <font face="courier" size=2 color="blue"> exec sp_helprotect 'EMPLOYEE'<br> <font face="courier" size=2 color="black"> <br> You see that you get a list of users with their permissions on that object?<br> Let's first create a script like so:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @TableName varchar(255) <br> <br> DECLARE MyCursor CURSOR FOR <br> &nbsp SELECT table_name FROM information_schema.tables <br> &nbsp WHERE table_type = 'base table' ORDER BY table_name<br> <br> OPEN MyCursor <br> <br> FETCH NEXT FROM MyCursor INTO @TableName <br> WHILE (@@FETCH_STATUS <>-1) <br> BEGIN <br> <br> &nbsp PRINT 'exec sp_helprotect '+@TableName<br> <br> &nbsp FETCH NEXT FROM MYCursor INTO @TableName <br> END <br> <br> CLOSE MyCursor <br> DEALLOCATE MyCursor<br> <br> <font face="courier" size=2 color="black"> This will not directly print any permissions. What it does is that the script will generate<br> all statements "sp_helprotect table_name" for all tables in the database.<br> However, a problem with that script is, that if on a table, none of the user has explicit permissions,<br> then an error is printed.<br> So, effectively, you can NOT run those statements in one batch, but rather one after the other.<br> Well, it's something, but it's not really great.<br> <br> <br> <U>(2). A better solution to list the explicitly granted rights to users:</U><br> <br> We can indeed do a much better job than was shown above. However, maybe it's "nice" to know about<br> the "sp_helprotect" system stored procedure.<br> <br> This time we have a better solution. You might like the statement below. It's just one simple query,<br> but it shows us all we need to know.<br> <font face="courier" size=2 color="blue"> <br> select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES<br> <br> <font face="courier" size=2 color="black"> Note: You might take a look at the system view "sysprotects". Here you will find the object_id's, user_id's,<br> and action id's, all in numbers ofcourse.<br> With a little work you can device a nifty query that will show you the information also.<br> However, the simple query above saves us that work.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section19">1.19. GET A LIST OF ALL COLUMNNAMES WITH ALL TABLENAMES, AND DATATYPES, USING TSQL:</h3><br> <font face="courier" size=2 color="black"> Sometimes, it just is handy to have an ordered list of all Columnnames, with their datatypes,<br> and corresponding Tablenames, of all tables in your database.<br> <br> Here are a few queries that will produce such a list.<br> <br> -- Script 1: for all SQL Server versions<br> <br> <font face="courier" size=2 color="blue"> SELECT substring(c.name, 1, 30) as "ColumName",<br> c.xtype, <br> substring(object_name(c.id),1,30) as "TableName", <br> substring(t.name,1,30) as "DataType"<br> FROM syscolumns c, systypes t<br> WHERE c.xtype=t.xtype<br> ORDER By object_name(c.id)<br> <br> Optionally you can use a AND (after the WHERE clause), like for example "AND object_name(c.id)='Orders'<br> <br> <br> <font face="courier" size=2 color="black"> -- Script 2: for SQL Server 2005/2008<br> <br> <font face="courier" size=2 color="blue"> SELECT * FROM INFORMATION_SCHEMA.COLUMNS -- get all info<br> <br> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS -- get selected info <br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section20">1.20. A FEW NOTES ABOUT THE "@@" FUNCTIONS:</h3><br> <font face="courier" size=2 color="black"> We already have seen a couple of functions is this note, like for example the string function "REPLACE()", or<br> the system functions (that are typically used by the DBA) like for example "fn_virtualfilestats()" etc..<br> <br> With a function, you would typically expect parentheses "(" and ")", where in between you can place the inputvalue(s),<br> and or expressions, like "myfunction(5)" or "substring(cust_code,1,8)".<br> <br> In SQL Server, there exists a rather special class of system functions.<br> Usually, they don;t take values from us, although for some functions a SET statement is allowed.<br> They have names like "@@functionname", so their names always start with "@@".<br> They behave in a sense like a Global variable, and you can retrieve it's value by selecting the functionname.<br> In a moment, you will see some examples.<br> The "context" in which they apply, is very divers: some functions have only meaning on the Instance Level (like @@cpu_busy),<br> while others can perfectly be used in your scripts (like @@error).<br> <br> We already have seen a few of those functions, like @@SPID, which is a "Session ID number" associated with every session.<br> <br> <B>Example 1:Some system statistical @@ functions</B><br> <br> Try the following statements in a Query Window:<br> <br> <font face="courier" size=2 color="blue"> select @@CPU_BUSY<br> select @@IO_BUSY<br> select @@IDLE<br> select @@Total_Errors<br> <br> <font face="courier" size=2 color="black"> These are just a few examples of those system statistical @@ functions.<br> For example, @@CPU_BUSY returns the time that SQL Server has spent working since it was last started.<br> As another example, @@Total_Errors represent the number of disk write errors encountered by SQL Server since last started.<br> <br> Ofcourse, it might not be directly trivial how you could benefit from those type of functions,<br> but maybe it's nice to know they exist.<br> <br> <B>Example 2: "@@version"</B><br> <br> This function returns a resultset, telling you the current version of SQL Server.<br> So, if we just do this:<br> <br> <font face="courier" size=2 color="blue"> select @@version<br> <br> <font face="courier" size=2 color="black"> we find the current version of this SQL Server installation.<br> <br> <B>Example 3: "@@rowcount" and "@@error"</B><br> <br> These are quite usable in your scripts. In section 1.13, we had created a script that removed "unwanted" characters<br> from a variable, or a field. Now suppose we want to clean a certain field in all rows of a table.<br> In other words, we want to walk through all rows of that table. Now you might think of using a cursor, or some<br> other form of a WHILE loop, using some sort of a counter that you need to construct.<br> Now take a look at the following example, with an quite interesting loop:<br> <br> <font face="courier" size=2 color="blue"> -- let's first create a test table:<br> <br> create table #a <br> (<br> s varchar(100)<br> )<br> <br> -- now insert a couple of testrecords, with good text and noise text:<br> <br> INSERT #a (s) SELECT 'asd i/.,<>as>[{}]vnbv'<br> INSERT #a (s) SELECT 'aaa'<br> INSERT #a (s) SELECT '123 ''h 9)'<br> <br> <font face="courier" size=2 color="black"> -- what's in the table now?<br> <br> SELECT * FROM #a<br> <br> asd i/.,<>as>[{}]vnbv<br> aaa<br> 123 'h 9)<br> <br> -- now let's clean the table:<br> <br> <font face="courier" size=2 color="blue"> while @@rowcount > 0<br> update #a<br> set s = replace(s, substring(s, patindex('%[^a-z^A-Z^0-9^ ]%', s), 1), '')<br> WHERE patindex('%[^a-z^A-Z^0-9^ ]%', s) <> 0<br> <br> <font face="courier" size=2 color="black"> -- So, what's in the table now?<br> <br> SELECT * FROM #a<br> <br> asd iasvnbv<br> aaa<br> 123 h 9<br> <br> So, our script worked. But the main thing is: did you noticed how we used the @@rowcount function,<br> as the "while true" criteria for the while loop? So, sometimes you can really simplify your WHILE loop,<br> because @@rowcount returns the number of rows of the last statement, or the number of rows associated <br> with the current TSQL block.<br> <br> But you can use it at ordinary SQL statements as well, if you want to know how many rows are processed.<br> For example, if you want to know how many rows were affected at an UPDATE statement, you can use @@rowcount as<br> in this example:<br> <br> <font face="courier" size=2 color="blue"> UPDATE EMPLOYEES<br> SET DEPARTMENT = 'Amsterdam' WHERE city = 'The Haque'<br> SELECT @@rowcount AS 'RowsChanged'<br> <br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section21">1.21. HOW TO SCRIPT YOUR DATABASE:</h3><br> <font face="courier" size=2 color="black"> It's easy to script a Database, including all objects (or a selection), using the graphical Management Studio.<br> To do that, rightclick your database, choose "Tasks", and next choose "Generate Scripts".<br> <br> But how to do it from the commandline, so that you can automate the process?<br> For this, you can use the "sqlpubwiz" utility.<br> To download it, you might use the following link:<br> <br> <a href="http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5498">download sqlpubwiz utility from Microsoft (english)</a><br> <br> The utility has quite a few prerequisites, but that's all described in the link above.<br> <br> Using it from the commandline, to script the SALES database, you would use a command<br> similar to the example below:<br> <br> <font face="courier" size=2 color="blue"> C:\> sqlpubwiz script -d SALES -S MYSERVER -U sa -P password C:\sources\sales.sql<br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section22">1.22. HOW TO GENERATE THE CORRESPONDING INSERT STAMENTS FOR A TABLE WITH DATA:</h3><br> <font face="courier" size=2 color="black"> Maybe you have a certain table with N rows, and you want to generate the corresponding N INSERT statements,<br> just as if you would have an empty table, and then could use those INSERT statements, to fill it up.<br> <br> <B>Method 1:</B><br> <br> Actually, the "sqlpubwiz" utility from section 1.21, can be used to achieve this.<br> You can script a complete database, either with structure and all data, or just only the structure of the objects.<br> So, for just one, or all tables, this tool might be of help. <br> To get further instruction, use the "help" function of the utility, like in<br> <br> C:\> sqlpubwiz help script<br> <br> By the way, if you do not need to use sqlpubwiz in scripts (in automated solutions), you might as well<br> enter the graphical mode, by just entering "sqlpubwiz" from the prompt, in the directory where the utility was installed.<br> <br> <br> <B>Method 2:</B><br> <br> You can create a stored procedure which will do the job: generate the insert statements for a given table.<br> You can find many free examples on the internet.<br> The code below, can be used for tables having administrative datatypes like char(), varchar(), decimal() etc..<br> When a table uses more "complicated" datatypes, the result may not be what you like.<br> <br> Create the procedure listed below, in your Database. Suppose you now want to create the INSERT statements for<br> the CUSTOMERS table, use the following query:<br> <br> <font face="courier" size=2 color="blue"> exec generate_inserts CUSTOMERS<br> <font face="courier" size=2 color="black"> <br> Source of the procedure:<br> <br> <font face="courier" size=2 color="blue"> create proc generate_inserts @table varchar(20)<br> --Generate inserts for table @table<br> AS<br> declare @cols varchar(1000)<br> declare @col varchar(50)<br> <br> set @cols=''<br> <br> declare colcur<br> cursor for<br> select column_name<br> from information_schema.columns<br> where table_name=@table <br> <br> open colcur<br> <br> fetch next from colcur into @col<br> <br> while @@fetch_status=0<br> begin<br> select @cols = @cols + ', ' + @col<br> <br> fetch next from colcur into @col<br> end<br> <br> close colcur<br> deallocate colcur<br> <br> select @cols = substring(@cols, 3, datalength(@cols))<br> <br> --select @cols<br> <br> declare @sql varchar(4000)<br> declare @colname varchar(100),<br> @coltype varchar(30)<br> <br> select @sql = 'select replace(''insert ' + @table + ' (' + @cols + ') '<br> <br> select @sql = @sql + 'values ('''<br> <br> declare ccur<br> cursor for<br> select column_name, data_type<br> from information_schema.columns<br> where table_name=@table<br> <br> open ccur<br> <br> fetch from ccur into @colname, @coltype<br> <br> while @@fetch_status=0<br> begin<br> if @coltype in ('varchar', 'char', 'datetime')<br> select @sql=@sql + ''''''<br> <br> select @sql=@sql + ' + coalesce(convert(varchar, ' + @colname + '), ''null'') + '<br> <br> if @coltype in ('varchar', 'char', 'datetime')<br> select @sql=@sql + ''''''<br> select @sql = @sql + ''', '''<br> <br> fetch from ccur into @colname, @coltype<br> end<br> <br> close ccur<br> deallocate ccur<br> <br> select @sql=substring(@sql, 1, datalength(@sql)-3)<br> <br> select @sql=@sql + ')'', ''''''null'''''', ''null'') from ' + @table<br> <br> exec (@sql)<br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section23">1.23. OVERVIEW SQL SERVER VERSIONS AND BUILDS v 7 - 2008:</h3><br> <font face="courier" size=2 color="black"> Sometimes it can be handy to have a simple listing of all SQL Server versions and builds.<br> You can find that list using the following link:<br> <br> <a href="sqlversions.txt">List of SQL Server versions (txt file)</a><br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section24">1.24. LOGGING FROM STORED PROCEDURES AND TSQL:</h3><br> <font face="courier" size=2 color="black"> Often, you want to run a stored procedure, or just a TSQL block, and afterwards you want to view<br> some logging of this procedure, in order to see if that code ran succesfully or not.<br> <br> There are many ways to get errors or actions logged.<br> For example, to a file, the screen (when you run it "now"), or a table, or the Windows application log etc...<br> Personally, I think that logging in a sort of Admin table is very effective.<br> And its easy: it's just an INSERT statement. <br> <br> let's view a couple of examples.<br> <br> Suppose we have a stored procedure which uses an input variable. Suppose this procedure takes as input<br> the path to a file (like E:\UPLOAD\DATA), and then performs some sort of processing, using that file.<br> <br> So, the start of the sp code could be something like this:<br> <br> <font face="courier" size=2 color="blue"> CREATE PROCEDURE usp_processfile<br> @FILE_PATH VARCHAR(128) -- the input var<br> <br> AS<br> BEGIN<br> -- the rest of the code<br> <br> <font face="courier" size=2 color="black"> Now, before you want to do any processing at all, you first want to check the "validity" of this<br> input variable. So, you want to know first that it's not NULL, and that it at least contains a "/" character somewhere,<br> which makes it likely that the input variable (which a user has typed in, or the procedure received from elsewhere),<br> is indeed a "path". So, we might have put this test in the beginning of our sp: <br> <font face="courier" size=2 color="blue"> <br> if @FILE_PATH IS NULL or (charindex('\', @FILE_PATH) = 0)<br> BEGIN<br> &nbsp SET @ERR_MESSAGE='Incorrect file path.'<br> &nbsp GOTO error_section<br> END<br> <br> <font face="courier" size=2 color="black"> Now let's take a look at a few examples on how we can log that errormessage.<br> <br> <font face="courier" size=2 color="blue"> <B><U>Logging to a table:</U></B><br> <br> error_section:<br> &nbsp INSERT INTO ADMINTABLE<br> &nbsp VALUES<br> &nbsp (getdate(),'usp_processfile',@ERR_MESSAGE)<br> <br> &nbsp PRINT @ERR_MESSAGE -- show to the screen as well<br> RETURN -- terminate procedure<br> <br> <B><U>Raising an error to a calling application:</U></B><br> <br> error_section:<br> RAISERROR (@ERR_MESSAGE, 10, 1) <br> RETURN -- terminate procedure<br> <br> <B><U>Raising an error to a calling application and write an event in the SQL Server log and Windows eventlog:</U></B><br> <br> error_section:<br> RAISERROR (@ERR_MESSAGE, 10, 1) with log<br> RETURN -- terminate procedure<br> <br> <B><U>Logging to a file:</U></B><br> <br> error_section:<br> DECLARE @log_cmd varchar(128)<br> SELECT @log_cmd='echo'+' '+@ERR_MESSAGE+' >> C:\MANAGEMENT\LOAD_FILE.LOG'<br> EXEC master.dbo.xp_cmdshell @log_cmd<br> <br> RETURN -- terminate procedure<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section25">1.25. USING "EXEC" IN TSQL AND STORED PROCEDURES:</h3><br> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="brown"> Important: if you use "exec" (or sp_executesql) to dynamically create SQL from strings and variables,<br> and if one or more variables comes from user input, then there exists the potential danger of "SQL injection".<br> This could mean that the resulting code could be something that you most definitely do not want to execute,<br> and it could even be dangerous.<br> This is especially true for code that's exposed through public sites, like in a backend database for a website.<br> So always check the variables very carefully.<br> <br> <font face="courier" size=2 color="black"> <B><U>Usage 1:</U></B><br> <br> In TSQL code, like TSQL batches and stored procedures, you might encounter the "exec" keyword.<br> <br> You probably have used it many times already, since if you want to execute a stored procedure from a Query Window,<br> you would use:<br> <br> exec StoredProcedureName<br> <br> So, if you want to <I>execute</I> existing code in for example stored procedures, extended stored procedures<br> (which could be stored in a module or .dll), you simply call them using "exec".<br> <br> This is very easy indeed. Here are a few other examples:<br> <br> <font face="courier" size=2 color="blue"> exec StoredProcedureName -- calling a regular sp from a query tool<br> <br> exec xp_ExtendedStoredProcedureName -- calling an extended sp from a query tool<br> <br> <font face="courier" size=2 color="black"> Now let's try this: <br> <font face="courier" size=2 color="blue"> <br> CREATE procedure usp_proc1 @input1 varchar(32)<br> AS<br> <br> BEGIN<br> PRINT 'Hi from usp_proc1'<br> PRINT @input1<br> END<br> GO<br> <br> CREATE procedure usp_proc2 @input2 varchar(32)<br> AS<br> <br> BEGIN<br> PRINT 'Hi from usp_proc2'<br> PRINT @input2<br> END<br> GO<br> <br> CREATE procedure usp_proc3 @input3 varchar(32)<br> AS<br> <br> BEGIN<br> PRINT 'Hi from usp_proc3'<br> PRINT @input3<br> END<br> GO<br> <br> <font face="courier" size=2 color="black"> Now let's create a Main routine, that calls all 3 upper sp's:<br> <font face="courier" size=2 color="blue"> <br> CREATE procedure usp_Main -- Create a sp which calls the other sp's<br> @inp1 varchar(32),<br> @inp2 varchar(32),<br> @inp3 varchar(32)<br> AS<br> <br> BEGIN<br> <br> &nbsp exec usp_proc1 @inp1<br> <br> &nbsp exec usp_proc2 @inp2<br> <br> &nbsp exec usp_proc3 @inp3<br> <br> END<br> GO<br> <br> <font face="courier" size=2 color="black"> If you would run usp_Main, like this:<br> <br> exec usp_Main 'Hi', 'Dear', 'Reader'<br> <br> you would get this:<br> <br> <font face="courier" size=2 color="brown"> Hi from usp_proc1<br> Hi<br> Hi from usp_proc2<br> Dear<br> Hi from usp_proc3<br> Reader<br> <br> <font face="courier" size=2 color="black"> <B><U>Usage 2:</U></B><br> <br> There is a second important application of "exec", namely to execute code which has been dynamically created<br> from characterstrings and variables.<br> Usually, it would take a form as:<br> <br> <font face="courier" size=2 color="brown"> exec ('a list of strings and/or character-variables which indeed linked together form a command')<br> <br> <font face="courier" size=2 color="black"> Or if a certain variable, for example @varcmd, is string already, and contains a command, then this works too:<br> <font face="courier" size=2 color="brown"> <br> exec @varcmd<br> <br> <font face="courier" size=2 color="black"> In either case, the concatenated total string will be parsed, and if it indeed forms a command, it's executed.<br> It's really great! Here are a few examples to illustrate this:<br> <br> <font face="courier" size=2 color="blue"> <B>Example 1:</B><br> <br> DECLARE @dbname VARCHAR(64)<br> <br> DECLARE cur1 CURSOR FOR<br> SELECT name FROM master.dbo.sysdatabases<br> <br> OPEN cur1<br> FETCH NEXT FROM cur1 INTO @dbname<br> <br> WHILE (@@fetch_status<>-1)<br> BEGIN<br> PRINT 'DATABASE FILES FOR: ' +@dbname<br> PRINT ' '<br> EXEC ('SELECT <br> fileid, <br> (size * 8 /1024) AS SIZE_IN_MB,<br> substring(name, 1, 32) AS NAME,<br> substring(filename, 1, 90) AS FILENAME <br> FROM '+@dbname+'.dbo.sysfiles')<br> <br> FETCH NEXT FROM cur1 INTO @dbname<br> END<br> <br> CLOSE cur1<br> DEALLOCATE cur1<br> <br> <br> <B>Example 2:</B><br> <br> SELECT @totalcommAND='bcp CUSTOMERS in '+@importpath+' -c -F2 -T'<br> EXEC xp_cmdshell @totalcommand<br> <br> <br> <font face="courier" size=2 color="black"> Notice how we concatenated serveral strings into @totalcommand, which we then feed to "exec".<br> The above code is often rewritten into the code below.<br> <br> <br> <font face="courier" size=2 color="blue"> SELECT @totalcommAND='bcp ##BCP_LOAD in '+@importpath+' -c -F2 -T'<br> EXEC @RESULT = xp_cmdshell @totalcommand<br> <br> <font face="courier" size=2 color="black"> Since the execution of "xp_cmdshell @totalcommand" will return a "returncode" (like 0, 1 etc..),<br> you can assign that to a variable like @RESULT or @RETVAL or something similar.<br> One advantage is that you can use @RETVAL for further tests, like shown below.<br> <br> <font face="courier" size=2 color="blue"> EXEC @RESULT = xp_cmdshell @totalcommand<br> IF (@RESULT <> 0)<br> BEGIN<br> &nbsp SET @MESSAGE='Error loading data in temporary table. Possibly wrong path or file not found.'<br> &nbsp GOTO error_section<br> END<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section26">1.26. HOW TO VIEW WHETHER PROCESSES WERE SQL Server, OR NTLM, OR KERBEROS AUTHENTICATED:</h3><br> <font face="courier" size=2 color="black"> In SQL Server 2005 and 2008, you can use the following query. It will show all spids, with their<br> associated loginname, hostname, program, and authentication method.<br> <br> <font face="courier" size=2 color="blue"> SELECT s.session_id, s.auth_scheme, e.host_name, e.program_name, e.nt_user_name<br> FROM sys.dm_exec_connections s, sys.dm_exec_sessions e<br> WHERE s.session_id=e.session_id<br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section27">1.27. A FEW NOTES ON USER DEFINED FUNCTIONS:</h3><br> <font face="courier" size=2 color="black"> There are plenty standard functions in SQL Server. In section 1.12, we have seen a couple of important<br> "string" functions, but there are lots of mathematical, and statistical, and other functions as well.<br> <br> You can write your on functions too. Then they are called "User Defined Functions" (udf).<br> <br> But we already can write our own <B>stored procedures</B>, so why do we need udf's?<br> <br> A stored procedure could indeed do all sorts of jobs. Maybe they do not even "return" something<br> to the calling user or program. Maybe it just updates a table, and that could be all.<br> <br> A function absolutely resembles a stored procedure, but generally, they should be smart compact code,<br> and <U>always</U> returns "something", like we are used from the standard functions in SQL Server.<br> It could return a "scalar" (like a number or string), or a "table-like" resultset, called a "table".<br> <br> They are great. You know that you always "exec" a stored procedure, but your udf's can be used like<br> the common standard functions. So, you can use them in all sorts of SELECT statements.<br> <br> A few simple examples will illustrate how to create and use them.<br> <br> <font face="courier" size=2 color="blue"> <B>Example 1:</B><br> <br> CREATE FUNCTION udfEmpByCity(@city varchar(20))<br> RETURNS TABLE<br> AS<br> RETURN (<br> &nbsp SELECT EmployeeID, LastName, FirstName<br> &nbsp FROM Employees<br> &nbsp WHERE (City=@City)<br> )<br> <br> To access the resultset of this function you can use the next example:<br> <br> SELECT * FROM udfEmpByCity('Seattle')<br> <br> <font face="courier" size=2 color="black"> User defined functions thus can accept parameters, AND returns either a scalar value or a tablelike dataset.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section28">1.28. HOW TO GET LISTS OF PK AND FK CONSTRAINTS:</h3><br> <font face="courier" size=2 color="black"> Although the INFORMATION_SCHEMA views are (see the system views container of your database) intended<br> to provide you all sorts of information, included PK and FK relations, the "older" systemviews (like sysreferences)<br> are still great. But you are certainly encouraged to take a look at the INFORMATION_SCHEMA views.<br> However, here I will stick to the "good old" (or acient) systemviews. Take a look at the following (simple) queries:<br> <br> <font face="courier" size=2 color="blue"> <B>-- Get a list of Referencing (FK) and Referred (PK) Tables:</B><br> <br> SELECT substring(object_name(constid), 1, 40) AS FK,<br> &nbsp substring(object_name(fkeyid), 1, 40) AS "Referencing Table",<br> &nbsp substring(object_name(rkeyid), 1, 40) AS "Referenced Table"<br> FROM sysreferences<br> ORDER BY object_name(rkeyid)<br> <br> <B>-- Get a list of Tables and their Primary Keys:</B><br> <br> SELECT substring(name,1,30) AS "PrimaryKey", <br> id, xtype, object_name(parent_obj) AS "Parent_table" <br> FROM sysobjects<br> WHERE xtype='PK'<br> ORDER BY object_name(parent_obj)<br> <br> <font face="courier" size=2 color="black"> Ofcourse, if you want to be a bit more "modern", just query views like INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS,<br> and INFORMATION_SCHEMA.COLUMNS, and INFORMATION_SCHEMA.TABLE_CONSTRAINTS etc..,<br> and find out what you can get from them.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section29">1.29. A FEW NOTES ON DYNAMIC SQL:</h3><br> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="brown"> Important: if you use "exec" (or sp_executesql) to dynamically create SQL from strings and variables,<br> and if one or more variables comes from user input, then there exists the potential danger of "SQL injection".<br> This could mean that the resulting code could be something that you most definitely do not want to execute,<br> and it could even be dangerous.<br> This is especially true for code that's exposed through public sites, like in a backend database for a website.<br> <br> <font face="courier" size=2 color="black"> Suppose you create TSQL code, then in that code, some strings and variables can be "concatenated"<br> in such a way, that a valid SQL has formed. Then you can really execute it.<br> <br> We already have done that on many occasions. For example:<br> <br> <font face="courier" size=2 color="blue"> Example 1:<br> <br> DECLARE @DBLIST TABLE ( -- variable of type "table"<br> ID INT IDENTITY(1,1),<br> DBNAME VARCHAR(32)<br> )<br> <br> DECLARE @DBNAME VARCHAR(32)<br> DECLARE @I INT<br> DECLARE @DBLIST_MAX INT<br> <br> INSERT INTO @DBLIST -- we fill the table variable with a list of our databases<br> (DBNAME)<br> SELECT name from sys.databases<br> <br> SET @DBLIST_MAX=(SELECT MAX(ID) FROM @DBLIST)<br> SET @I=1<br> <br> WHILE @I <= @DBLIST_MAX<br> BEGIN<br> &nbsp SET @DBNAME=(SELECT DBNAME FROM @DBLIST WHERE ID=@I)<br> &nbsp PRINT 'SOME INFO PER DATABASE USING sp_helpdb: '+@dbname<br> &nbsp PRINT ' '<br> &nbsp <B>EXEC ('sp_helpdb '+@dbname)</B><br> &nbsp SET @I=@I+1 -- increase the counter by "1"<br> END<br> <br> <font face="courier" size=2 color="black"> Yes, we already illustrated the use of "exec" in section 1.25, but maybe it was a good idea to list a couple<br> of more example.<br> Up to now, we always have used the "exec" keyword, to execute a command that was composed of strings alone<br> or strings and variables. There is much more to "exec" than what is shown here, for example, you might take a look<br> in BOL (Books Online) and search for the keyword "exec" for the complete syntax.<br> <br> <B>sp_executesql:</B><br> <br> As another way of executing dynamically created sql, we can take a look at the very versitile<br> "sp_executesql" stored procedure. The use of "exec" and "sp_executesql" are very similar at first sight.<br> Both will let your TSQL block of code, execute any dynamically build DML or DLL SQL statement.<br> <br> But there are important differences as well:<br> <br> <ol> <li> Both can execute a command composed of strings and variables.</li> <li> In many occasions, sp_executesql will reuse an execution plan, because to the optimizer, it "looks" like<br> as just another stored procedure with having input parameters. This is an advantage over just using "exec",<br> especially if your code will be used heavily.</li> <li> sp_executesql can be <I>parameterized</I></li> <li> Generally, sp_executesql seems to be regarded as "more secure" than just using "exec".</li> </ol> Let's illustrate sp_executesql by showing a few examples.<br> <br> <B>Example 1:</B><br> <br> <font face="courier" size=2 color="blue"> exec sp_executesql N'CREATE TABLE ABC1 (<br> ID INT NULL,<br> CUSTNAME VARCHAR(20))'<br> <br> <font face="courier" size=2 color="black"> So, what's the big deal here? Using just exec, we can do the same thing:<br> <br> <font face="courier" size=2 color="blue"> exec ('CREATE TABLE ABC2 (<br> ID INT NULL,<br> CUSTNAME VARCHAR(20))')<br> <br> <font face="courier" size=2 color="black"> Yes, it works the same. But in the next example, we use code that accepts a parameter,<br> which can easily be done using sp_executesql.<br> <br> <B>Example 2:</B><br> <br> CREATE TABLE T1 (<br> cust_id int,<br> cust_name varchar(20))<br> <br> insert into T1<br> values<br> (1,'Harry')<br> <br> Now let's use sp_executesql using a parameter:<br> <br> <font face="courier" size=2 color="blue"> DECALE @varcmd nvarchar(128)<br> DECLARE @ID INT<br> <br> SET @varcmd='SELECT * FROM T1 WHERE CUST_ID=@ID'<br> <br> exec sp_executesql @varcmd, N'@ID INT', @ID=1<br> <br> <font face="courier" size=2 color="black"> <B>Example 3:</B><br> <br> As another example of how comfortable sp_executesl can be, consider the following.<br> Suppose you have some fancy backupcommand constructed, and you have "put" that into the variable @cmd.<br> This variable @cmd expects a parameter, let's say @backup_start_date.<br> Then you might have constructed a command like below, to let it all work:<br> <br> <font face="courier" size=2 color="blue"> exec sp_executesql @cmd,@params=N'@backup_start_date datetime', @backup_start_date = @start_date<br> <font face="courier" size=2 color="black"> <br> <B>Example 4:</B><br> <br> This example can be quite usefull at times. Suppose you declare a "very wide" character variable, say @SQL,<br> of datatype VARCHAR(1000), or better yet VARCHAR(2000), or even still better, NVARCHAR(MAX).<br> As the following example will show, you can put all sorts of "SQL" into this variable (it's just a string),<br> and when done, you can PRINT @SQL, and even EXEC @SQL:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @SQL NVARCHAR(MAX)<br> <br> SET @SQL=' &nbsp &nbsp -- note the starting quote<br> CREATE TABLE A (id int)<br> CREATE TABLE B (id int)<br> ' &nbsp &nbsp -- note the closing quote<br> <br> PRINT (@SQL)<br> <br> EXEC (@SQL) -- this will execute all sql statements in @SQL<br> <br> <font face="courier" size=2 color="black"> The statement EXEC @SQL will thus create the tables A and B. <br> Ofcourse, if you need "to do other stuff" before you PRINT or EXEC the @SQL variable, you can add<br> strings later on to @SQL, like for example:<br> <br> <font face="courier" size=2 color="blue"> DECLARE @SQL NVARCHAR(MAX)<br> <br> SET @SQL=' <br> CREATE TABLE A (id int)<br> CREATE TABLE B (id int)<br> ' <br> <br> -- now do all sorts of other code<br> <br> SET @SQL=@SQL +'<br> CREATE TABLE C (id int) -- or other strings<br> ' <br> <br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section30">1.30 A FEW NOTES ON BACKUP AND RESTORE USING TSQL STATEMENTS:</h3><br> <font face="courier" size=2 color="black"> If you want to see some basic Backup and Restore methods, using TSQL Statements, you might want<br> to take a look at this document:<br> <br> <a href="quick_backup_restore_sqlserver.htm">Quick Backup & Restore SQL Server (html)</a><br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section31">1.31 A FEW NOTES ON REVIVING SQL ACCOUNTS AFTER MOVE (OR RESTORE) OF A DATABASE:</h3><br> <font face="courier" size=2 color="black"> If you move, or restore a Database from one SQL Server on machine A, to another SQL Server installation<br> on machine B, the SQL database accounts are not "in sync" with the SQL logins (on B) anymore.<br> <br> The following short note shows how to deal with this, using the special stored procedures<br> "sp_change_users_login" and "sp_help_revlogin".<br> <br> <a href="revivesqlaccounts.txt">Short note on reviving SQL accounts, after moving or restore of a Database on another machine.</a><br> <br> <br> <br> <H3>Some Further Excercises:</H3> <br> <font face="arial" size=2 color="blue"> <h3 id="section33">2.1. Excercise: CODE TO GET THE BASIC CREATE TABLE STATEMENTS:</h3><br> <font face="courier" size=2 color="black"> Surely, much better scripts exists than what you will find below. That's not the point.<br> The script below, will <B>ONLY</B> produce the basic "CREATE TABLE" statements, without the<br> PK and FK constraints, check constraints etc... So, it is only meant as an exercise.<br> <br> It's just straightforward programming with variable declarations, loops etc..<br> <br> It might be instructive to walk through the code. It contains many elements of what was discussed above.<br> <br> <font face="courier" size=2 color="blue"> DECLARE @TABNAME VARCHAR(128)<br> DECLARE @COLNAME VARCHAR(128)<br> DECLARE @COLTYPE VARCHAR(128)<br> DECLARE @COLLENGTH INT<br> DECLARE @COLSTART INT<br> DECLARE @COLEND INT<br> DECLARE @COLCOUNT INT<br> DECLARE @IS_NULLABLE VARCHAR(3)<br> DECLARE @PRINTNULL VARCHAR(10)<br> DECLARE @PRECISION INT<br> DECLARE @SCALE INT<br> DECLARE @COLSTRING VARCHAR(128)<br> <br> SET NOCOUNT ON<br> <br> DECLARE @TABMETA TABLE<br> (<br> I INT IDENTITY(1,1),<br> COLUMN_NAME VARCHAR(128),<br> DATA_TYPE VARCHAR(128),<br> CHARACTER_MAXIMUM_LENGTH VARCHAR(128),<br> IS_NULLABLE VARCHAR(3),<br> PRECISION INT,<br> SCALE INT<br> )<br> <br> DECLARE cur1 CURSOR FOR<br> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS --registers all columns of all tables<br> <br> OPEN cur1<br> FETCH NEXT FROM cur1 INTO @TABNAME -- get the first tablename<br> <br> WHILE (@@fetch_status<>-1)<br> BEGIN<br> <br> DELETE FROM @TABMETA<br> <br> -- get all column information of the table (in this loop), and put it into the tablevariable @TABMETA<br> <br> INSERT INTO @TABMETA<br> (COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,PRECISION,SCALE)<br> SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE, NUMERIC_PRECISION,NUMERIC_SCALE<br> FROM INFORMATION_SCHEMA.COLUMNS <br> WHERE TABLE_NAME=@TABNAME ORDER BY ORDINAL_POSITION ASC<br> <br> SELECT @COLCOUNT=(SELECT MIN(I) FROM @TABMETA)<br> SELECT @COLEND=(SELECT MAX(I) FROM @TABMETA)<br> <br> <br> PRINT 'CREATE TABLE '+@TABNAME+' ('<br> WHILE @COLCOUNT<=@COLEND<br> BEGIN <br> &nbsp SELECT @COLNAME=(SELECT LTRIM(RTRIM(COLUMN_NAME)) FROM @TABMETA WHERE I=@COLCOUNT)<br> &nbsp SELECT @COLTYPE=(SELECT LTRIM(RTRIM(DATA_TYPE)) FROM @TABMETA WHERE I=@COLCOUNT)<br> &nbsp SELECT @COLLENGTH =(SELECT CHARACTER_MAXIMUM_LENGTH FROM @TABMETA WHERE I=@COLCOUNT)<br> &nbsp SELECT @IS_NULLABLE=(SELECT IS_NULLABLE FROM @TABMETA WHERE I=@COLCOUNT)<br> &nbsp SELECT @PRECISION=(SELECT PRECISION FROM @TABMETA WHERE I=@COLCOUNT)<br> &nbsp SELECT @SCALE=(SELECT SCALE FROM @TABMETA WHERE I=@COLCOUNT)<br> <br> &nbsp IF @IS_NULLABLE='NO'<br> &nbsp &nbsp SELECT @PRINTNULL='NOT NULL'<br> &nbsp ELSE<br> &nbsp &nbsp SELECT @PRINTNULL='NULL'<br> <br> &nbsp IF @COLTYPE LIKE '%char%' <br> &nbsp&nbsp SELECT @COLSTRING=@COLNAME+' '+@COLTYPE+'('+CAST(@COLLENGTH as VARCHAR)+')'+' '+@PRINTNULL<br> &nbsp ELSE<br> &nbsp &nbsp &nbsp IF @COLTYPE IN ('decimal','nummeric')<br> &nbsp &nbsp &nbsp SELECT @COLSTRING=@COLNAME+' '+@COLTYPE+'('+CAST(@PRECISION as VARCHAR)+','+CAST(@SCALE as VARCHAR)+')'+' '+@PRINTNULL<br> &nbsp &nbsp &nbsp ELSE <br> &nbsp &nbsp &nbsp SELECT @COLSTRING=@COLNAME+' '+@COLTYPE+' '+@PRINTNULL<br> <br> &nbsp IF @COLCOUNT<@COLEND<br> &nbsp SELECT @COLSTRING=@COLSTRING+',' <br> <br> &nbsp PRINT @COLSTRING<br> <br> &nbsp SELECT @COLCOUNT=@COLCOUNT+1<br> END<br> PRINT ')'<br> <br> -- get a newline between the create table statements<br> PRINT char(13)<br> <br> FETCH NEXT FROM cur1 INTO @TABNAME<br> END<br> <br> CLOSE cur1<br> DEALLOCATE cur1<br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section34">2.2. Excercise: SIMPLE EXAMPLE OF AN AUTOMATED TSQL BACKUP SCRIPT:</h3><br> <font face="courier" size=2 color="black"> Read the following example carefully.<br> <br> DECLARE @dbname VARCHAR(100)<br> DECLARE @DATE DATETIME<br> DECLARE @BACKUP_DATE VARCHAR(128)<br> DECLARE @FILE VARCHAR(128)<br> DECLARE @FULLFILE VARCHAR(128)<br> DECLARE @EXTENSION VARCHAR(128)<br> <br> SELECT @DATE=GETDATE()<br> SELECT @BACKUP_DATE=CONVERT(VARCHAR(10),@DATE,20)<br> SELECT @FILE='\\backupsrv\sqlbackup$\sqlclus8a\' -- Attn: place here the right location like F:, or \\server\share<br> SELECT @EXTENSION='.dmp'<br> <br> DECLARE cur1 CURSOR FOR<br> SELECT name<br> FROM master.dbo.sysdatabases where name not in ('tempdb')<br> <br> OPEN cur1<br> FETCH NEXT FROM cur1 INTO @dbname<br> <br> WHILE (@@fetch_status<>-1)<br> BEGIN<br> SELECT @FULLFILE=@FILE+@dbname+@BACKUP_DATE+@EXTENSION<br> <br> <font face="courier" size=2 color="blue"> -- If you only want to PRINT the backup statements, then uncomment the PRINT statement<br> -- If you want the script to execute the backup statements, then uncomment the EXEC statement<br> <font face="courier" size=2 color="black"> <br> -- EXEC ('BACKUP DATABASE '+@dbname+' TO DISK='''+@FULLFILE+''' WITH INIT ') <br> -- PRINT 'BACKUP DATABASE '+@dbname+' TO DISK='''+@FULLFILE+''' WITH INIT ' <br> <br> FETCH NEXT FROM cur1 INTO @dbname<br> END<br> <br> CLOSE cur1<br> DEALLOCATE cur1<br> <br> -- End of Script<br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section35">3.1. OLDER FILE LISTING QUERIES AND COMMANDS:</h3><br> <font face="courier" size=2 color="black"> Some time ago, when SQL 7 and SQL 2000 were the current versions, as time went by, I created a txt file<br> listing DBA like queries and all sorts of example statements.<br> Still, a lot is usable for 2005 and 2008. For example, the use of many 'stringfunctions' have not changed,<br> so you might still find some usefull stuff in here.<br> <br> If you want to view it, use this link:<br> <br> <a href="sqlserver.txt">Listing of SQL Server 2000 query examples (txt file)</a><br> <br> <br> <br> <br> <br> <br> <br> <br> </body> </html>