New Codeplex Project Extreme T-SQL Script

Hello,
yesterday, I created http://etsql.codeplex.com/. spelling   Extreme T-SQL Script.

This allows me to publish my scripts using version control.

The initial scripts deal with filling long text/varchar(max) by sql script and the reverse to display the whole content of such data fields: I create update statements which would recreate the content or even move it to ORACLE databases.

But be warned, I'm not allways trying to present completly generall solutions. My priority is on cases I have to tackle at work. I.e. currently I only handle data with a resonable frequency of embedded newlines.

Often I think, that T-SQL isn't the best language for some of these database related task. But it surely is the most spread language dealing with database manipulation. That means using T-SQL I reach four times as much coworkers than with Powershell and it is easy to use once SSMS is running.

Bernd

sptab -- My replacement for many uses of sp_help

You can use sp_help or SSMS to find out the columns in a table, view or stored procedure parameter list.
But to use sp_help, you need to know the exact name of the object, which I usually refuse to remeber (based on inconsistant naming conventions).

Instead I use some sort of intellisence logic.

If I don't give an exact name, tell me which match, otherwise give me the information I ask for.

And here is my code (requires SQL-2005):

if object_id ('sptab', 'P') > 0     drop procedure sptab
go

create proc dbo.sptab (@BeginOfString varchar(50)= null, @type varchar(10) = '', @sort varchar(10) = 'id')
as

SET NOCOUNT ON

declare @n       varchar(100)
declare @obid    int
declare @tmp_obj table(id_ int, name_ varchar(256), type varchar(5))

if @BeginOfString is null
  begin
    print 'usage sptab [, type ][, sort (id|name|len)]    BK 2010-10-08'
    return 0
  end

if @type = ''
    insert into @tmp_obj select id, name, type from sysobjects where type in ('U', 'V', 'P', 'FN', 'IF') and name = rtrim(@BeginOfString)
else
    insert into @tmp_obj select id, name, type from sysobjects where type = @type and name = rtrim(@BeginOfString)

if @@ROWCOUNT = 1
    select @n = name_, @obid = id_ from @tmp_obj      
else
  begin
    delete @tmp_obj
    if @type = ''
        insert into @tmp_obj select id, name, type from sysobjects where type in ('U', 'V', 'P') and name like rtrim(@BeginOfString) + '%'   
    else
        insert into @tmp_obj select id, name, type from sysobjects where type = @type and name like rtrim(@BeginOfString) + '%'   

    if @@ROWCOUNT = 1               
        select @n = name_, @obid = id_ from @tmp_obj      
    else
        begin
        select type, name_ name from @tmp_obj order by 1
        return 0
        end
  end

print @n

if @sort = 'id'
    select  left (c.name, 40) col, c.colid, left (t.name, 16) type, t.xtype, c.length
     , case when isnullable <> 0 then 'J' else '' end NULLs
     , case when COLUMNPROPERTY(@obid, c.name ,'IsIdentity') <> 0 then 'J' else '' end Id
     , isnull(OBJECT_DEFINITION (c.cdefault), '') [default]
    from syscolumns c
        join systypes t on c.xtype = t.xtype and c.usertype = t.usertype
    where c.id = @obid
    order by c.colid
else if @sort = 'name'
    select  left (c.name, 40) col, c.colid, left (t.name, 16) type, t.xtype, c.length
     , case when isnullable <> 0 then 'J' else '' end NULLs
     , case when COLUMNPROPERTY(@obid, c.name ,'IsIdentity') <> 0 then 'J' else '' end Id   
     , isnull(OBJECT_DEFINITION (c.cdefault), '') [default]
    from syscolumns c
        join systypes t on c.xtype = t.xtype and c.usertype = t.usertype
    where c.id = @obid
    order by col
else if @sort = 'len'
    select  left (c.name, 40) col, c.colid, left (t.name, 16) type, t.xtype, c.length
     , case when isnullable <> 0 then 'J' else '' end NULLs
     , case when COLUMNPROPERTY(@obid, c.name ,'IsIdentity') <> 0 then 'J' else '' end Id   
     , isnull(OBJECT_DEFINITION (c.cdefault), '') [default]
    from syscolumns c
        join systypes t on c.xtype = t.xtype and c.usertype = t.usertype
    where c.id = @obid
    order by length, colid


go

find_nonconstant_columns in Database Tables

If your database is designed by Lord Voldemort, I guess that its tables are highly denormalized, worse they contain a lot of redundant columns.

Here I present a power-muggle tool to get rid of all columns containing the same value or calling it the other way, to keep the columns with different values.

Beeing a muggle, here I have a very different opinion about the equality of null values than the ANSI-Wizzards, who are proud of making simple syntax weired.

set nocount on

if OBJECT_ID ('find_nonconstant_columns', 'P' ) > 0 drop procedure find_nonconstant_columns
go

create procedure dbo.find_nonconstant_columns( @tableName sysname)
as
begin

declare @res table (colname sysname, count int, has_nulls int)
Declare @typname varchar(30)   
Declare @name varchar(60)
Declare @value varchar (100)
declare @cmd varchar(max)
declare @result varchar(max)

-- select name from systypes

Declare c CURSOR FOR
    select c.name, t.name
    from syscolumns c
        join sysobjects o on c.id = o.id
        join systypes t on c.xtype = t.xtype
        where o.name = @TableName
OPEN c
FETCH NEXT From c into @name, @typname
WHILE @@FETCH_STATUS = 0
begin
    -- select name from systypes -- I guess I must cast a few more types
    set @value = case when @typname in ('text') then 'cast( ' + @name + ' as varchar(max) )' else  @name  end
    -- this statement is dedicated to all ansi fools who enforce Bad Influenced syntax
    set @cmd = 'select ''' + @name +''',  (select count(distinct '+@value+') from '+@TableName
        +' where '+@name+' is not null), (Select max (case when ' + @name + ' is null then 1 else 0 end) from '+@TableName+')'
    --print @cmd
    insert into @res  exec( @cmd )
    FETCH NEXT From c into @name, @typname
end
CLOSE c
DEALLOCATE c

select * from @res

Declare r cursor for
    Select colname from @res where (count + has_nulls) > 1
OPEN r
FETCH NEXT From r into @name
WHILE @@FETCH_STATUS = 0
begin
    set @result = case when @result is null then @name else @result + ', ' + @name end
    FETCH NEXT From r into @name
end
close r
deallocate r

print @result


end