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

Use SSRS ReportViewer with PowerShell, use parameters and catch navigate event

Today I will just show a small extension of code from @sqlbelle which you find at SQL Server PowerShell : How to View your SSRS Reports (rdl) Using PowerShell and ReportViewer

Thanks belle, your code gave me a great start to  build a prototyp and evaluate the ReportViewer Control. I had to add an eventhandler for the hyperlink event and to provide reportparameters by code.

As I didn't find these features in PowerShell Examples and I found the translating of C# not trivial, I present here my code.

Thanks go to James Kovacs for the C# example for Reportparameters

James Brundage how showed me how to write an eventhandler in PowerShell

and somewhere in Bruce Payette's Windows PowerShell in Action I found the solution for defining the array of .Net Objects, which I need here for  Report Parameters.

hope it helps

Bernd

Edited:
Oops I had still formating difficulties in this blog.
You find the same code at http://poshcode.org/1977.

Some Interessting SQL-Server queries which work without any special Demo Database

Now that SQLPSX 2.2.2 is out. I will give you some sql queries you can try immediately provided you can connect to any SQL-Server database.

My list includes some print statements, which do not work in the current release, but the feature comes soon. BTW this list includes many things you possibly want to know about your database. If you know similar interessting queries let me know.

---- generell queries not depending on special demo databases

-- about the current Server --
Select @@SERVERNAME;
Select @@VERSION;
SELECT SERVERPROPERTY('productversion')  Version;
SELECT SERVERPROPERTY ('productlevel') ServicePack;
SELECT SERVERPROPERTY ('edition')  Edition;
Select name from master.sys.databases order by database_id;
Select name from master.sys.databases order by 1;
select * from master..sysdatabases;

-- set dbcmptlevel to SQL 2000
declare @dbname sysname; select @dbname = db_name(0);exec sp_dbcmptlevel @dbname, 80;
-- set dbcmptlevel to SQL 2005
declare @dbname sysname; select @dbname = db_name(0);exec sp_dbcmptlevel @dbname, 90;
-- set dbcmptlevel to SQL 2008
declare @dbname sysname; select @dbname = db_name(0);exec sp_dbcmptlevel @dbname, 100;
---
declare @curdb sysname; select  @curdb =  db_name(0);
  select 1 lfd, left(@curdb, 20)   db,  DATABASEPROPERTYEX(@curdb, 'Collation') Collation
union
  select 2, left('master', 20) db,  DATABASEPROPERTYEX('master', 'Collation')
union
  select 3, left('tempdb', 20) db,  DATABASEPROPERTYEX('tempdb', 'Collation')
order by 1;
---


-- about the current database --

Select db_name(0);
select @@LANGUAGE;
select @@DATEFIRST;
set language german;
set language us_english;

-- others
Select getdate();


-- not yet supported
Print 'Text No 1 Note all print output is before any resultsets';
Select getdate();
Print 'Text No 2 In Query Analyzer the date would show up before this 2nd print output';
RAISERROR('dispays immediately', 0, 1) WITH NOWAIT
declare @dbname sysname; select @dbname = db_name(0);exec sp_dbcmptlevel @dbname;

Determine the rowcounts for all tables of a SQL-Server Database

Of course you can loop through all tables of a database, compose a select statement to query count(*) of each single table and end in some time-out problem at some customer database. 
There is a quick solution without any loops:

SELECT o.name, max(i.rows) FROM dbo.sysindexes i  join sysobjects o on i.id = o.id
group by o.id, o.name
order by 1

 I'm not 100 % certain that the results are exact in all cases, but for me it works well.

Cf. http://entwickler-forum.de/showthread.php?t=18850

Generating the column list for an SQL-Server Table

How often did you use in query analyzer extra | options | results | userdefined delimiter| ,
to the column list of a table in a form ready to replace the '*' in

Select * from mytable

by the complete column list?

Well I definitely did it too often. It is so easy to write a stored procedure for the same purpose:

if object_id('build_columnlist') > 0
    drop procedure build_columnlist
go

create procedure dbo.build_columnlist @table_name sysname
as
begin
declare @cols varchar(max)  -- varchar(8000) for sql server 2000

select @cols = case when @cols  is null then c.name else @cols + ', ' + c.name end
from syscolumns c join sysobjects o on c.id = o.id
where o.name = @table_name order by colorder

select @cols
end

go

now just type

exec build_columnlist 'mytable'

and when you display the result as text, it is there ready to copy and past.

Oops did you change extra | options | max characters pro column to 8000 ?
That was for Query Analyzer, SQL-Managemantstudio has a similar setting. 

And note the trick of updating a variable using a select statement.

defaults are always wrong.

Bernd