SIMPLE T-SQL to generate create table scripts

just run this in the database you want to script out --- use "Results to Text" mode: EXEC sp_msforeachtable 'SELECT ''Create table ? ('' UNION ALL SELECT CASE WHEN rn > 1 THEN '' ,'' ELSE '' '' END + x FROM (SELECT TOP 100 percent ''[''+COLUMN_NAME + ''] '' + DATA_TYPE + CASE WHEN DATA_TYPE IN (''varchar'',''char'') THEN ''('' + CAST(CHARACTER_MAXIMUM_LENGTH as varchar) + '')'' ELSE '''' END + '' ''+ case when IS_NULLABLE = ''YES'' THEN ''NULL'' ELSE ''NOT NULL'' END x ,ROW_NUMBER() OVER(ORDER BY COLUMNS.ORDINAL_POSITION) rn FROM INFORMATION_SCHEMA.columns WHERE COLUMNS.TABLE_NAME = replace(replace(''?'',''[dbo].['',''''),'']'','''') ) x UNION ALL SELECT '')'' ' outputs runnable create table script

turn any list of text into a table

If you're like me and you regularly get people emailing you long lists of .. whatever.. zip codes, addresses, VIN numbers, etc .. and they want you to do something with each item in the list, but the lists are always inconsistent and you spend a lot of time just cleaning up the list so you can move forward with the task, here's a handy quick cleaner upper I made. it will turn this:

lkfdjhhdf 2356345 dfsdsf dfds df fd dsffd sad,dsa,,DS,F,,FSADFSDF, DF

into a nice table:

first of all, you need this function. Split parameter string from comma seperator in SQL IN clause then we do this:

DECLARE @item_list VARCHAR(4000)
SET @item_list = 
'
lkfdjhhdf
2356345

dfsdsf

dfds

df
fd

dsffd
sad,dsa,,DS,F,,FSADFSDF,
DF
'

DECLARE @c VARCHAR(4000) = REPLACE(REPLACE(REPLACE(REPLACE(@item_list,CHAR(13)+CHAR(10),','),',,',','),',,',','),',,',',')
IF RIGHT(@c,1) = ',' SET @c = LEFT(@c,LEN(@c)-1)
IF LEFT(@c,1) = ',' SET @c = RIGHT(@c,LEN(@c)-1)

IF RIGHT(@c,1) = ',' SET @c = LEFT(@c,LEN(@c)-1)
IF LEFT(@c,1) = ',' SET @c = RIGHT(@c,LEN(@c)-1)

set nocount on
DECLARE @list AS TABLE (item VARCHAR(100))

INSERT @list
SELECT [sID] FROM redbumperdb.dbo.UF_CSVToTable(@c)

SELECT * FROM @list AS l

Set Based way to order items with SQL

so you have a list of rules or something and you want your users to be able to change the processing order

DECLARE @new_item_priority INT = 1
DECLARE @reorder_priority AS TABLE (item_id INT, item_priority INT )

INSERT @reorder_priority 
SELECT item_id, ROW_NUMBER() OVER(ORDER BY rn) AS item_priority
FROM  (
    SELECT item_id, 
    CASE 
    WHEN item_id = @p_item_id AND @new_item_priority < item_priority THEN CAST(@new_item_priority AS FLOAT) -.1
    WHEN item_id = @p_item_id AND @new_item_priority > item_priority THEN CAST(@new_item_priority AS FLOAT) +.1
    ELSE item_priority
    END rn
    FROM dbo.items
    -- WHERE clause if needed..
) x
ORDER BY rn


UPDATE i
    SET item_priority = rp.item_priority
FROM dbo.items i
JOIN @reorder_priority rp ON rp.item_id = i.item_id