Tag

#SQL

Posts

• ArticleMar 12, 2013

  SSIS - Nightly import package that updates and inserts records

  Build an SSIS package that imports from a source view, updating existing records and creating new ones, using Lookup and Conditional Split.

  2 min read#SQL#Tools
• SnippetSep 18, 2012

  SQL search for a string in a database

  Generate a set of SELECT statements that search every text column in the database for a given string.

  select
  'select distinct ''' + tab.name + '.' + col.name + '''  from
  [' + tab.name + '] where [' + col.name + '] like ''%TEXT_TO_SEARCH%'' union '
  from sys.tables tab
  join sys.columns col on (tab.object_id = col.object_id)
  join sys.types types on (col.system_type_id = types.system_type_id)

  1 min read#SQL
• SnippetAug 16, 2012

  SQL comma separated list

  Build a comma separated string from the values of a table column with a single COALESCE query.

  DECLARE @listStr VARCHAR(MAX)
  SELECT @listStr = COALESCE(@listStr + ''',''', '') + ColumnName
  FROM TableName
  SELECT @listStr

  1 min read#SQL
• SnippetMar 27, 2012

  SQL List All Triggers In Database

  A query that returns every trigger in a database with its table, type and whether it is disabled.

  USE YourDBName
  GO
  SELECT trigger_name = sysobjects.name,
        trigger_owner = 'x',
        table_schema = s.name,
        table_name = OBJECT_NAME(parent_obj),

  1 min read#SQL
• SnippetMar 22, 2012

  SQL List All Tables In Database

  Return all tables in a SQL Server database by querying sys.tables.

  USE YourDBName
  GO
  SELECT *
  FROM sys.tables

  1 min read#SQL
• SnippetMar 20, 2012

  SQL List All Stored Procedures

  List every stored procedure in a database using INFORMATION_SCHEMA.ROUTINES.

  SELECT * FROM
  DatabaseName.information_schema.routines
  WHERE routine_type = 'PROCEDURE'

  1 min read#SQL
• SnippetMar 15, 2012

  SQL Proper Casing

  A T-SQL user defined function that converts text to proper case, capitalising the first letter of every word.

  CREATE FUNCTION ProperCase(@inpStr nvarchar(8000))
  RETURNS nvarchar(8000) AS
  BEGIN
       DECLARE @Result nvarchar(8000)
       SET @inpStr = LOWER(@inpStr) + ' '
       SET @Result = ''

  1 min read#SQL