Improving speed and efficiency using T-SQL to concatenate data

I personally prefer to avoid cursor-based looping in T-SQL or PL/SQL because of the obvious performance gains. This blog post is for a friend who needed a quick way of looping through and building a string in T-SQL without using cursors.

– Function to concatenate records in a single variable using set-based processing SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE FUNCTION fnGenerateSkillXML (@intSkillGroupID numeric) RETURNS varchar(8000) AS BEGIN DECLARE @strReturn VARCHAR(8000) SET @strReturn = ” SELECT @strReturn = @strReturn + ‘<SKILL><SKILL_ID>’ + convert(varchar, SKILL_ID) + ‘</SKILL_ID><SKILL_NAME>’ + SKILL_NAME + ‘</SKILL_NAME></SKILL>’ FROM VW_DISTINCT_SKILLS_BY_SKILL_GROUP WHERE SKILL_GROUP_ID = @intSkillGroupID ORDER BY SKILL_NAME   return @strReturn   END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

 

–Application SELECT  distinct skill_group_id,  dbo.fnGenerateSkillXML(skill_group_id) FROM VW_DISTINCT_SKILLS_BY_SKILL_GROUP

RESULT

101    <SKILL><SKILL_ID>102…..
102    <SKILL><SKILL_ID>103…..
103    <SKILL><SKILL_ID>108…..
104    <SKILL><SKILL_ID>122…..

List all tables in a database

Listing all tables in a SQL Server database, including the option to filter

– All tables EXEC sp_tables – Alternatively, if you need to filter further SELECT Owner = TABLE_SCHEMA, TableName = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘IsMsShipped’) = 0 ORDER BY TABLE_SCHEMA, TABLE_NAME

Disable and Enable Referential Integrity

The below T-SQL allows you to disable all constraints, run any database cleanup script you may need to and then re-enable the constraints.

– disable referential integrity for all tables in the database EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’ GO – enable referential integrity again for all tables in the database EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’ GO