Notes

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Programming - SQL - Microsoft SQL======

=====Code Snippets=====
====Get the size of all tables in a database====
<code tsql>
DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 
select [name]
from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT *
FROM #TempTable
ORDER BY CAST(LEFT(ReservedSize,(LEN(ReservedSize) - 3)) AS INT)

--Final cleanup!
DROP TABLE #TempTable

GO

</code>


====Show size of tables in tempdb====
<code tsql>
SELECT TBL.name AS ObjName 
      ,STAT.row_count AS StatRowCount 
      ,STAT.used_page_count * 8 AS UsedSizeKB 
      ,STAT.reserved_page_count * 8 AS RevervedSizeKB 
FROM tempdb.sys.partitions AS PART with(nolock)
     INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT with(nolock)
         ON PART.partition_id = STAT.partition_id 
            AND PART.partition_number = STAT.partition_number 
     INNER JOIN tempdb.sys.tables AS TBL with(nolock)
         ON STAT.object_id = TBL.object_id 
ORDER BY STAT.reserved_page_count desc,TBL.name;
</code>


====Generate SQL Statements=====
Code:
<code tsql>
SELECT 'DROP TABLE g2user.' + name FROM sys.tables
</code>
Partial output:
<file>
DROP TABLE g2user.g2_SequenceLock
DROP TABLE g2user.g2_SequenceId
DROP TABLE g2user.g2_ExternalIdMap
DROP TABLE g2user.g2_FailedLoginsMap
DROP TABLE g2user.g2_AccessMap
</file>
Code:
<code tsql>
SELECT 'EXECUTE sp_rename ''g2user.' + name + ''', ''' + replace(name,'g2test','g2')+ '''' FROM sys.tables
</code>
Partial output:
<file>
EXECUTE sp_rename 'g2user.g2test_SessionMap', 'g2_SessionMap'
EXECUTE sp_rename 'g2user.g2test_TkOperatnMap', 'g2_TkOperatnMap'
EXECUTE sp_rename 'g2user.g2test_TkOperatnMimeTypeMap', 'g2_TkOperatnMimeTypeMap'
EXECUTE sp_rename 'g2user.g2test_TkOperatnParameterMap', 'g2_TkOperatnParameterMap'
</file>

===A DROP DATABASE line per database on the server===
<code tsql>
SELECT 'DROP DATABASE [' + name + ']' FROM SYS.Databases
</code>
Partial output:
<file>
DROP DATABASE [master]
DROP DATABASE [tempdb]
DROP DATABASE [model]
DROP DATABASE [msdb]
</file>

For all databases except the system databases:
<code tsql>
SELECT 'DROP DATABASE [' + name + ']' FROM SYS.Databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
</code>

====SQL Logins====
[[http://weblogs.sqlteam.com/BILLG/archive/2010/07/08/Scripting-out-SQL-Server-Logins.aspx|Bill Graziano's SQL Server Blog - Scripting out SQL Server Logins]] \\

===Associate SQL login with db login===
Associate a newly created SQL login account with an existing user in a restored database:
<code tsql>
ALTER USER loginnaam WITH LOGIN = loginnaam
</code>

====Shrinking the transaction log====
Transaction log multiple GB in size, database a few hundred MB and no need to keep the transaction log?

Then try the following after making a backup of the database:

<code tsql>
BACKUP LOG [databasename] WITH truncate_only
USE [databasename]
DBCC SHRINKFILE ('databasename_log', 0)
</code>

Where databasenaam_log must be replaced by the logical name of the log (examine the properties of the database in SQL Server Management Studio).

===Shrinking the transaction log on SQL Server 2012===
On SQL Server 2012 (and higher?) the truncate_only option has been removed. \\
To remove the transaction log in SQL Server 2012 the recovery model of the database must be switched to simple and then back to full. After this you should be able to shrink the transaction log file.

Use the following SQL statement:
<code tsql>
--Set it to simple
USE master
ALTER DATABASE [databasename] SET RECOVERY SIMPLE

---Set it to full
USE master
ALTER DATABASE [databasename] SET RECOVERY FULL
</code>

Sources: \\
[[http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic24278.aspx|sql newsgroups - Live server running out of space - Can't reduce Transaction Log!]] \\
[[http://home.hot.rr.com/graye/Articles/SQL_LogMaintenance.htm|SQL Server Log File Maintenance]] \\
[[http://technet.microsoft.com/en-us/library/ms186865(v=sql.110).aspx|Microsoft TechNet - SQL Server 2012 - BACKUP (Transact-SQL)]] \\
[[http://technet.microsoft.com/en-us/library/ms189272(v=sql.110).aspx|Microsoft TechNet - SQL Server 2012 - View or Change the Recovery Model of a Database (SQL Server)]] \\

===Open transactions===
If your database mode is Simple and you have a log file of several GB with no current activity on the database server to justify such a large logfile, you may have an open transaction in the log file.

Using the following you can check the ID and start date of the oldest open transaction in the log:

<code tsql>
USE [databasename]
DBCC OPENTRAN
</code>

If there are open transactions, the result should look something like this:

<file>
Transaction information for database 'DB'.

Oldest active transaction:
    SPID (server process ID): 67
    UID (user ID) : -1
    Name          : implicit_transaction
    LSN           : (26506:575:6)
    Start time    : Sep  7 2011  8:50:07:497AM
    SID           : 
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</file>

If there are no open transactions, the result should look like this:

<file>
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</file>

If you are sure that the open transaction is no longer necessary, you can end it with:

<code>
KILL spid
</code>

Where spid must be replaced by the server process ID from the result of DBCC OPENTRAN. \\
You might have to repeat this several times to kill all open transactions. Always make sure that the open transaction you want to kill isn't worth saving. \\
After this you can retry the log shrink.

See also: \\
[[http://blogs.msdn.com/b/suhde/archive/2009/03/16/shrinking-the-transaction-log-files-in-sql-server.aspx|MSDN Blogs > Blogs from Suhas > Shrinking the Transaction Log files in SQL Server]]

====Single/Multi user mode====
Place a database in single user mode:

<code tsql>
ALTER DATABASE db-name SET SINGLE_USER
</code>

Place a database in multi-user mode: 

<code tsql>
ALTER DATABASE db-name SET MULTI_USER
</code>

Source:[[http://www.cryer.co.uk/brian/sqlserver/howto_set_database_single_user.htm|Set a single database into Single User Mode]] \\

====Repair database====
Force a database repair allowing data loss:

<code tsql>
DBCC CHECKDB ('dbnaam', repair_allow_data_loss)
</code>

====Add column to existing table====
The following creates a new column with data type char(1):

<code tsql>
ALTER TABLE tabelnaam
ADD nieuwekolomnaam char(1) 
</code>

Source:[[http://www.techonthenet.com/sql/tables/alter_table.php|TECH on the net - SQL: ALTER TABLE Statement]] and [[http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx|MSDN - Transact-SQL Reference (SQL Server 2000) ALTER TABLE]] \\

====Count lines====
<code tsql>
SELECT COUNT (*) FROM TableName
</code>

====Generating an insert statement====
The following snippet inserts IP-adresses starting from 10.1.12.100-10.1.12.130 to 10.1.18.100-10.1.18.130 and two 0 values to a table. Modify numbers, run once as-is and then remove the "---" before the insert to actually apply the changes to the table.
<code tsql>
DECLARE @i INT,@i2 INT,@String VARCHAR(30)

SELECT @i = 12,@i2 = 100,@String = '10.1.'

WHILE @i <= 18
BEGIN

	WHILE @i2 <= 130
	BEGIN
	
		---INSERT TableName
		SELECT @String + CAST(@i AS VARCHAR) + '.' + CAST(@i2 AS VARCHAR),0,0
		
		SELECT @i2 = @i2 + 1
	
	END

	SELECT
		@i = @i + 1,
		@i2 = 128

END
</code>

====Connection info====
<code tsql>
SELECT db_name(dbid) as DatabaseName,hostname,program_name,loginame,spid
---SELECT *
FROM sys.sysprocesses
WHERE dbid > 0
AND db_name(dbid) = 'databasenaam'
</code>
Or info from one of the following:
<code tsql>
SELECT * FROM sys.dm_exec_connections 
SELECT * FROM sys.dm_exec_sessions
</code>

Show locks:
<code tsql>
SELECT db_name(resource_database_id) AS DatabaseName,* FROM sys.dm_tran_locks
</code>

Sources: \\
[[http://www.sqlservercurry.com/2008/03/how-to-see-active-connections-for-each.html|SQL Server Curry - How to see active connections for each Database in SQL Server 2005]] \\
[[http://www.itechtalk.com/thread2031.html|itechtalk - Monitoring Processes in T-SQL]] \\

====Database Recovery Model====
<code tsql>
SELECT name,recovery_model_desc
FROM master.sys.databases
--WHERE recovery_model_desc LIKE '%full%'
--WHERE recovery_model_desc LIKE '%simple%'
</code>


====Get a list of jobs running longer than 00:59:00====

Get a list of job runs that ran longer than 59 minutes (run_duration > 5900):

<code tsql>
SELECT *
FROM msdb.dbo.sysjobhistory
WHERE 
	step_id = 0
	AND run_duration > 5900
</code>


====Generate a statement per database to get the list of files per database====

<code tsql>
SELECT 'SELECT * FROM [' + name + '].sys.database_files'
FROM sys.databases;
</code>