different column occurrences - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

different column occurrences

Author

Topic

jemacc
Starting Member

42 Posts

Posted - 2004-10-20 : 02:27:22

Hi all,
I have a follwing cursor that i will like to change.
I will like to get all of the different occurrences in a column
The bold area is where I will like to make the change but I cannot figure it out. Any help be greatly appreciated.

Posted - 04/14/2003 : 06:33:27
--------------------------------------------------------------------------------

/*This script gives a list of all the columns in the database and the number of distinct values in them. This can be very useful if you take over a database and you want to find out if the columns are actually used. It might also come in handy if you need some information about the cardinality of your columns to help you design indexes.

It also gives information about the largest size of data in a column and the actual size of the column, so you can find out if those text columns are really necessary for example.

Although the script includes a few performance optimisations, it is not advisable to run it against a production database, as it will perform a table scan on all the tables multiple times.

Jacco */

create procedure sp_tableinfo
as
DECLARE @table sysname
DECLARE @column sysname
DECLARE @datatype sysname
DECLARE @designed_length int
DECLARE @all_count int
DECLARE @sql nvarchar(4000)

SET NOCOUNT ON 
EXEC sp_updatestats

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Will speed things up a bit

CREATE TABLE #table_info 
(table_name sysname NOT NULL
,column_name sysname NOT NULL
,data_type sysname NOT NULL
,designed_length int NULL
,max_length int NULL
,distinct_count int NULL
,all_count int NOT NULL
,cardinality AS 
CASE 
WHEN distinct_count IS NULL THEN CAST(data_type AS varchar(7))
WHEN all_count = 0 THEN CAST('No rows' AS varchar(7))
ELSE CAST(
CAST(CAST(distinct_count AS decimal)/CAST(all_count AS decimal) AS decimal(18,4)) AS varchar(7))
END
)

DECLARE c CURSOR FAST_FORWARD FOR 
SELECT 
isc.table_name, 
isc.column_name, 
isc.data_type, 
COALESCE(isc.character_maximum_length, isc.numeric_precision),
si.rowcnt
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
INNER JOIN sysindexes si
ON isc.table_name = OBJECT_NAME(si.id)
WHERE ist.table_type = 'base table'
AND ist.table_name not like 'dt%'
AND si.indid IN (0,1)
ORDER BY isc.table_name, isc.column_name

OPEN c
FETCH NEXT FROM c INTO @table, @column, @datatype, @designed_length, @all_count
WHILE @@FETCH_STATUS = 0
BEGIN
IF @datatype IN ('text', 'ntext', 'image')
BEGIN
SET @sql = 'SELECT ''' + @table + ''', ''' + @column + ''', ''' + @datatype + ''''
SET @sql = @sql + ', ' + CAST(@designed_length AS varchar(10)) + ', MAX(DATALENGTH([' + @column + ']))'
SET @sql = @sql + ', NULL' + ', ' + CAST(@all_count AS varchar(10)) + ' FROM [' + @table + ']'
END
ELSE
BEGIN
SET @sql = 'SELECT ''' + @table + ''', ''' + @column + ''', ''' + @datatype + ''''
SET @sql = @sql + ', ' + CAST(@designed_length AS varchar(10)) + ', MAX(LEN(CAST([' + @column + '] AS VARCHAR(8000))))'
SET @sql = @sql + ', COUNT(DISTINCT [' + @column + '])'
SET @sql = @sql + ', ' + CAST(@all_count AS varchar(10)) + ' FROM [' + @table + ']'
END
PRINT @sql
INSERT INTO #table_info (table_name, column_name, data_type, designed_length, max_length, distinct_count, all_count)
EXEC(@sql)
FETCH NEXT FROM c INTO @table, @column, @datatype, @designed_length, @all_count
END
CLOSE c
DEALLOCATE c

SELECT table_name, column_name, data_type, designed_length, max_length, distinct_count, all_count, cardinality
FROM #table_info

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DROP TABLE #table_info

a_shyam41
Starting Member

9 Posts

Posted - 2004-10-20 : 05:45:55

Remove the cursor and just use CASE statement inside SELECT statment for @datatype variable:
ex:
SET @sql = 'SELECT ... CASE WHEN ''' + @datatype + ''' IN '''(''ntext'', ''text'', ''image'') THEN ....'

You need not use cursor just to have different select statmenets for different datatypes which can be achieved just by using CASE statements as above.....

Hope this helps

jemacc
Starting Member

42 Posts

Posted - 2004-10-21 : 11:28:58

I thank you for your response but I am just getting more farmiliar with cursor and based on your example which section of the cursor will I have to remove. This cursor gives me the count of all columns and data type but what I am trying to accomplish is to get each occurrence of each column from every table in the database

for example
table1
column1
str----- 0
st------ 3
ave----- 5
Ln -----200

table1
column2
123----- 7
550----- 8
300----- 25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources