Scripting SQL Query 1

[dr00bie]

I have the following query,

SELECT MAX(DATALENGTH([Members Present])) AS MembersPresentLenMax
FROM tblIPPCore2

In the tblIPPCore2 table, there are 20 or 30 ntext fields, I need to find the maximum length so that I can restructure the table.

I would like to find out if it is possible to create a script that will run this query, but replace the column name and alias name with column names from the table.

Is this possible?

Thanks in Advance,
Drew

 

[hmckillop]

try this

declare @v_tempTable TABLE (myid bigint identity(1,1), sSQL varchar(1000)) 
DECLARE  @vMaxCount int, @iCount int, @sSQL varchar(1000)

INSERT into @v_tempTable (sSQL)
SELECT 'SELECT MAX(DATALENGTH([' + column_name + '])) AS ' + replace(column_name, ' ', '') + 'len FROM tblIPPCore2'
FROM information_schema.columns 
where table_name = 'tblIPPCore2'
SET @vMaxCount =@@ROWCOUNT 

set @iCount = 1
WHILE @iCount < @vMaxCount + 1
BEGIN
	SELECT @sSQL = sSQL from @v_tempTable where myid = @iCount
	exec (@ssql)
	SET @iCount = @iCount + 1
END

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dr00bie]

That is GREAT! But how can I catch errors from the column names?

The DB was upsized from Access, and is pretty bad shape... An example of a misnamed column name,

Review of Problems/Objectives

So when I try to make an alias, it fails... it should be like this, ReviewofProblemsObjectives

Thanks,
Drew

 

[dr00bie]

Thanks! I just renamed the offending columns.

Thanks a bunch that is a great script!


Drew

 

[hmckillop]

no problem, thanks for the star

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dr00bie]

Hey hmckillop...

I have made this into a SP that has one var @TableName. How can I use this SP with Access (ADP) to make a report? Seems that the select is looped so it may not work, can you suggest any options?

Thanks,
Drew

 

[hmckillop]

The stored proc should look something like

create proc usp_myProc
( @pi_TableName varchar(100) )
AS
declare @v_tempTable TABLE (myid bigint identity(1,1), sSQL varchar(1000)) 
DECLARE  @vMaxCount int, @iCount int, @sSQL varchar(1000)

INSERT into @v_tempTable (sSQL)
SELECT 'SELECT MAX(DATALENGTH([' + column_name + '])) AS ' + replace(column_name, ' ', '') + 'len FROM ' + @pi_TableName 
FROM information_schema.columns 
where table_name = @pi_TableName 
SET @vMaxCount =@@ROWCOUNT 

set @iCount = 1
WHILE @iCount < @vMaxCount + 1
BEGIN
    SELECT @sSQL = sSQL from @v_tempTable where myid = @iCount
    exec (@ssql)
    SET @iCount = @iCount + 1
END

It should work fine, the only thing though is I am not sure about Access handling mutliple resultsets.
A minor change and a actual physical temp table would allow you to get all the results back in one go
e.g.

create proc usp_myProc
( @pi_TableName varchar(100) )
AS
declare @v_tempTable TABLE (myid bigint identity(1,1), sSQL varchar(1000)) 
CREATE TABLE ##GlobalTempTable (MyLengthField bigint, MyFieldName varchar(100))

DECLARE  @vMaxCount int, @iCount int, @sSQL varchar(1000)

INSERT into @v_tempTable (sSQL)
SELECT 'INSERT INTO ##GlobalTempTable(MyLengthField , MyFieldName ) SELECT MAX(DATALENGTH([' + column_name + '])) AS ' + replace(column_name, ' ', '') + 'len, ' + replace(column_name, ' ', '') + 'len  FROM ' + @pi_TableName 
FROM information_schema.columns 
where table_name = @pi_TableName 
SET @vMaxCount =@@ROWCOUNT 

set @iCount = 1
SET NOCOUNT ON
WHILE @iCount < @vMaxCount + 1
BEGIN
    SELECT @sSQL = sSQL from @v_tempTable where myid = @iCount
    exec (@ssql)
    SET @iCount = @iCount + 1
END

SELECT * FROM ##GlobalTempTable --returns all the data in one result set with 1st column actual length and secondcolumn the name.

havent checked the exact syntax, but should do the job.

"I'm living so far beyond my income that we may almost be said to be living apart