HawkeSoft SQL Tips and Tricks - Part 6 - The Power of OBJECTPROPERTY

OBJECTPROPERTY is your one-stop shopping function for finding out all kinds of system information on database objects. It is especially powerful for triggers, contraints, and indexes. The sample code below shows one of my favorite uses for this function, finding disabled and untrusted constraints. This can be extremely useful when bad data has been introducted into a system, or to check clean up code after a data migration/conversion. (SQL 2000/2005)

SELECT name, tbl = object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsDisabled') = 1

SELECT name, tbl = object_name(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsNotTrusted') = 1

To read more check out http://msdn.microsoft.com/en-us/library/ms176105.aspx.

HawkeSoft SQL Tips and Tricks - Part 5 - Move tempdb's Data and Log Files

A common oversite when setting up SQL Server is to leave the system database's data and log files on the system (C:) drive. These databases generally stay at a small size except for one, tempdb. tempdb can grow quickly and unexpectedly, bringing your server down before you know it. To easily move tempdb's files to a safer, larger drive simply customize the code below. (SQL Server 2000/2005)

ALTER DATABASE tempdb MODIFY FILE (name=tempdev, filename='D:\MSSQL\Data\tempdb.mdf')

ALTER DATABASE tempdb MODIFY FILE (name=templog, filename='D:\MSSQL\Data\templog.ldf')

HawkeSoft SQL Tips and Tricks - Part 4 - Script All Triggers in a Database

Triggers in SQL Server are often treated as being directly tied to the table they fire off of. This makes sense in most scenarios but sometimes they need to be treated more as code objects. Most SQL tools make scripting triggers out a sub function of scripting tables. This script allows you to script out all triggers on a given database without concerning yourself with the tables (SQL Server 2005, large script).

Make sure to run this script in TEXT output mode and have a large buffer in your output window to handle large triggers.

-- USE the database you wish to script all triggers out of
USE [Database]
GO

SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[DBAGenerateTriggerScript]
(
@TriggerName varchar(255)
)
AS
/******************************************************************************
** Name: DBAGenerateTriggerScript
** Desc: Generates the DROP and CREATE statements for the given @TriggerName.
** IMPORTANT: Run this script in TEXT output mode
** Make sure you have a large buffer in your output window to handle large triggers
**
** Auth: ghawkes
** Date: 07/07/2008
*******************************************************************************/

SET NOCOUNT ON

-- Check to make sure the stored procedure exists
IF(NOT EXISTS(SELECT object_id
FROM sys.all_objects SO
WHERE SO.type = N'TR'
AND (SO.name = @TriggerName AND SCHEMA_NAME(SO.schema_id )= N'dbo')))
BEGIN
PRINT '''' + @TriggerName + ''' is not a valid trigger on the ''' + DB_NAME() + ''' database.'
RETURN
END

DECLARE @NewLine char(2)
SET @NewLine = CHAR(13) + CHAR(10)

-- Generate the comment header
SELECT '/***** Object: Trigger [dbo].[' + @TriggerName + '] Script Date: ' + CONVERT(varchar, GETDATE(), 100) + ' *****/' + @NewLine + @NewLine

UNION ALL

-- Generate the DROP statement
SELECT 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TriggerName + ']'') AND type in (N''TR''))' + @NewLine +
'DROP TRIGGER [dbo].[' + @TriggerName + ']' + @NewLine + 'GO' + @NewLine + @NewLine

UNION ALL

SELECT 'SET ANSI_NULLS ON' + @NewLine +
'GO' + @NewLine +
'SET QUOTED_IDENTIFIER ON' + @NewLine +
'GO'

UNION ALL

-- Generate the CREATE statement
SELECT
ISNULL(SM.definition, SSM.definition)
FROM sys.all_objects SO
LEFT OUTER JOIN sys.sql_modules AS SM ON SM.object_id = SO.object_id
LEFT OUTER JOIN sys.system_sql_modules AS SSM ON SSM.object_id = SO.object_id
WHERE SO.type = N'TR'
AND (SO.name = @TriggerName AND SCHEMA_NAME(SO.schema_id )= N'dbo')

UNION ALL

SELECT @NewLine + 'GO' + @NewLine
GO

-- Invoke the trigger script creation procedure for all triggers on the database
DECLARE @NewLine char(2)
SET @NewLine = CHAR(13) + CHAR(10)

-- Generate the USE statement
SELECT 'USE [' + DB_NAME() + ']' + @NewLine + 'GO' + @NewLine + @NewLine
DECLARE @SQL varchar(MAX)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC DBAGenerateTriggerScript ''' + ST.name + '''' + ' '
FROM sys.triggers ST

EXEC(@SQL)
GO

-- Drop the trigger script creation procedure
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBAGenerateTriggerScript]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DBAGenerateTriggerScript]
GO

HawkeSoft SQL Tips and Tricks - Part 3 - Getting Excel Column Headers

It has been a while since we have posted SQL Server Tips and Tricks so today is a 2 for 1 special!

Sometimes database administrators and data architects need to paste table result sets into Microsoft Excel. This is easy to do but sometimes the column list is so long that re-typing it can be tedious. In SQL Server 2000 copying from the results window in Enterprise Manager would retain the column headers. For some reason this functionality appears to be removed from SQL Server 2005 SSMS.

The code below will generate the column header row for easy pasting into Excel, Word, etc... For best results run the script in grid output mode and make sure you have a large buffer for the output window (SQL Server 2005).

DECLARE @TableName varchar(255)
SET @TableName = 'TableName'

DECLARE @TableID int
SELECT @TableID = object_id
FROM sys.tables
WHERE [name] = @TableName

IF(@TableID IS NULL)
BEGIN
PRINT 'Table [' + @TableName + '] does not exists in database ' + DB_NAME() + '.'
RETURN
END

DECLARE @SQL varchar(max),
@MaxColumnID int
SET @SQL = 'SELECT '
-- Find the max column ID for the fencepost problem
SELECT @MaxColumnID = MAX(column_id)
FROM sys.columns
WHERE object_id = @TableID

SELECT @SQL =
CASE WHEN column_id <> @MaxColumnID
THEN @SQL + '''' + [name] + ''' AS [Column' + CAST(column_id AS varchar(4)) + '], '
ELSE @SQL + '''' + [name] + ''' AS [Column' + CAST(column_id AS varchar(4)) + ']'
END
FROM sys.columns
WHERE object_id = @TableID
ORDER BY column_id ASC

EXEC(@SQL)

HawkeSoft SQL Tips and Tricks - Part 2 - Determine Exact SQL Server 2005 Version

Sometimes it can be a real pain to find something as simple as version information. The script below will return such information as product version, level, edition, and more (SQL Server 2005).

SELECT SERVERPROPERTY('productversion') AS [Product Version],
SERVERPROPERTY ('productlevel') AS [Product Level],
SERVERPROPERTY ('edition') AS [Edition]
SELECT @@VERSION AS [Version]
SELECT @@MICROSOFTVERSION AS [Microsoft Version]

HawkeSoft SQL Tips and Tricks - Part 1 - Writing SQL with SQL

This is the first in a series of posts describing SQL tips and tricks for SQL Server 2000/2005/2008. We at HawkeSoft hope you enjoy this series, and as always, we welcome your feedback!

Writing SQL with SQL:
Want to know the number of rows in every table in your database? No problem, until you realize your database has hundreds of tables. Try the code sample below for a quick dynamic SQL solution (SQL Server 2005).

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT COUNT(1) AS [' + ST.name + ' Count] FROM ' + ST.name + ' ' FROM sys.tables ST

--SELECT @SQL
EXEC(@SQL)

As you can see this approach is very powerful and can be applied in many different situations such as database maintenance commands, code re-compilations, updating statistics, etc...

If this type of code interests you should also check out the undocumented stored procedures sp_MSforeachtable and sp_MSforeachdb.

Remember, when dealing with code that has the potential to update several objects at once in your database ALWAYS make sure it is going to do what you anticipate, and consider the performance impacts. Happy coding!