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

Is your Clientready for SQLPSX with Oracle?

For those, who do not know, SQLPSX is a Codeplex project using PowerShell to access SQL-Server Databases using SMO and ADO.NET to acces SQL-Servers for adminstrative tasks and to execute querys on them. 

The newest release brought some integration into ISE the grafical Interactive Scripting Environment. You may look at http://www.youtube.com/watch?v=1KcNSHn7oTA to get an impression.

The modules OracleClient and OracleISE are new too and aim at establishing connections to and executing simple queries on Oracle servers. (Stored procedures returning refcursors and parameters are not yet supported).

Today I'm not describing how to use these modules, but I provide a test for you, to check if the needed components are installed.

The needed components are called Oracle Data Access Components
You need a computer with a orcale client including these components, you need PowerShell V2 running on it and you need how to connect to an Oracle Server using TNS names. 

Now lets execute a litte PowerShell, to see if the Oracle.DataAccess is found on your system

$a = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")             
$a | fl            

If you get something like

CodeBase               : file:///C:/Windows/assembly/GAC_32/Oracle.DataAccess/2
                         .111.6.0__89b483f429c47342/Oracle.DataAccess.dll
EntryPoint             :
EscapedCodeBase        : file:///C:/Windows/assembly/GAC_32/Oracle.DataAccess/2
                         .111.6.0__89b483f429c47342/Oracle.DataAccess.dll
FullName               : Oracle.DataAccess, Version=2.111.6.0, Culture=neutral,
                          PublicKeyToken=89b483f429c47342
GlobalAssemblyCache    : True
HostContext            : 0
ImageFileMachine       :
ImageRuntimeVersion    : v2.0.50727
Location               : C:\Windows\assembly\GAC_32\Oracle.DataAccess\2.111.6.0
                         __89b483f429c47342\Oracle.DataAccess.dll
ManifestModule         : Oracle.DataAccess.dll
MetadataToken          :
PortableExecutableKind :
ReflectionOnly         : False

the needed assembly is present and you can start to wait for me telling you how to use the modules.

Bernd

About the use of SQL-Server Print Statement

In the old days, when I did my ad-hoc SQL using T-SQL in the Query-Analyzer, it was natural to use print statements, when to give back some feedback to the user.

Today using SQL-Server Management Studio, half the time I display the results in grid and as long as it looks reasonable, I won't switch to the message tab.

With SQL-Server the use  print in T-SQL is rather easy. While in former days you could only emit one string or variable, with todays versions you can use expressions.

I'm not going to speak about Orcales DBMS_OUTPUT package in length here. Remember SET server output on. And OK with 10 g they even got SET SERVEROUTPUT ON SIZE UNLIMITED, i.e. it became an option to use.

But acessing your database from applications or a PowerShell module like SQLPSX,it becomes more difficult to collect the print output.
For SQL-Server it would be possible, that means I know code to get the output.
While with Oracle I have only some vague ideas, how to do it (requiring to use connections) .

Conculsion: I'm starting to rewrite my scripts, using select @msg msg instead of print @msg.

Bernd

sel tablename, 'comma-separated-list-of-columnnumbers'

And now the promised stored procedure:

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

create procedure dbo.sel (
    @tablename sysname,
    @mode varchar(255),
    @order int = 1,
    @where varchar(1000) = ''
) as
begin
    -- Bernd Kriszio 2010-05-07
    declare @cmd varchar(8000)
    declare @columnquery varchar(8000)
    declare @cnt integer
    declare @t_Name sysname
    declare @column_Name sysname
    declare @type integer
    Declare @anz integer

    Declare @table_names table(bez sysname)

    insert into @table_names select name from sysobjects where type in ('U', 'V') and name like (@tablename + '%')

    select @anz = count(1) from  @table_names
    if (@anz) = 1
    begin
        set @t_Name = (select bez from @table_names)
        Print @t_Name
    end
    else if (@anz > 0)
    begin
        Select 'ambigious '+ bez from @table_names
        return
     end

    if substring(@mode,1,1) = '*'
    begin
        set @cmd = 'Select * from ' + @t_Name + ' ' + @where + ' order by ' + str(@order)
        print @cmd 
        exec (@cmd)
    end

    else
    begin
        set @cmd = ''
        if (Select count(*) from splitColumnNumberList(@mode, ',')) = 1
        begin
            set @cnt = @mode
            declare column_cursor cursor for
                select c.name from syscolumns c join sysobjects o on c.id = o.id where o.name = @t_Name and colorder <= @cnt
        end
        else
            declare column_cursor cursor for
                select c.name from syscolumns c
                    join sysobjects o on c.id = o.id
                    join splitColumnNumberList( @mode, ',') on colid = value
                where o.name = @t_Name
                order by lfd
        open column_cursor
        FETCH NEXT From column_cursor into @column_Name
        WHILE @@FETCH_STATUS = 0
        begin
            --Print @column_Name
            set @cmd = case when @cmd  = '' then 'Select ' + @column_Name else @cmd + ', ' + @column_Name end
            FETCH NEXT From column_cursor into @column_Name
        end
        close column_cursor
        deallocate column_cursor
        set @cmd = @cmd + ' from ' + @t_Name + ' ' + @where + ' order by '+ str(@order)
        print @cmd
        exec (@cmd)
    end   


end
go

Hello SQL devellopers,

this is my first post using SQL-Scripts and I have to find out, which are the right tools, to present sql code here on blogger.com.

I present a small helper function which I will use to write a wrapper around the SQL Select-statement. Some academic egg-heads want to make me believe that columns have to be addressed by name, I'm going to teach them how to it by column numbers ( in my next post).

Second this is an example of my coding style. I do not use VARCHARN, [] (the damned reincarnation  of quoted identifiers) and I use the dbo. prefix at sensible places (contrary to the codegenerator in SQL-Server Management Studio ;.-).

This style has matured during one decade of SQL coding. My use of () brackets around the function parameters is nonstandard, but it works.

My style is develloped with the goal to help me convert T-SQL to PLSQL.



if object_id ('splitColumnNumberList') > 0
    drop function splitColumnNumberList
go

create function dbo.splitColumnNumberList
(
    @list varchar(8000),
    @del varchar(255) = ','
) returns @numbers table (lfd int identity(1,1),value int)
as
begin
    declare @pos    integer
    -- select CHARINDEX(',', '1,2,3')
    set @pos = CHARINDEX(@del, @list)
    while @pos > 0
        begin
            insert into  @numbers values(substring(@list, 1, @pos - 1))
            set @list = substring(@list, @pos + len(@del), 8000)
            set @pos = CHARINDEX(@del, @list)
        end

    insert into  @numbers values (@list)

    return
end

go

Hello SQL world

I created this blog, to keep SQL stuff from my main blog http://pauerschell.blogspot.com/ which is mostly about PowerShell.

I guess the focus will be on SQL-Server stored procedures.


You find my on
twitter as @bernd_k,
but if you are not interessted in PowerShell, WPF, WPK and want to focus on database stuff follow me on
twitter as @sqlsulidae.

Bernd