November 18, 2009 at 12:59 pm
You can write a script by getting the column names of the table using
select column_name from INFORMATION_SCHEMA.COLUMNS where table_name='table1'
Insert the column_name into temp table and write while to read each column and execute the sql stmts. Below script will give your output for specified tablename
DECLARE @ColName VARCHAR(20)
DECLARE @Str VARCHAR(100)
DECLARE @ColCount INT
DECLARE @Ctr INT
CREATE TABLE #TEMP1(RowNo INT IDENTITY(1,1),COLUMN_NAME varchar(20))
INSERT INTO #TEMP1
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='tablename'
SET @ColCount=@@ROWCOUNT
SET @CTR=1
WHILE (@Ctr <= @ColCount)
BEGIN
SELECT @ColName =COLUMN_NAME FROM #TEMP1 WHERE RowNo=@Ctr
SET @Str='SELECT MIN(LEN('+ @colname +')) as minlength FROM tablename'
EXEC(@Str)
SET @Str='SELECT MAX(LEN('+ @colname +')) as minlength FROM tablename'
EXEC(@Str)
SET @Str='SELECT TOP 10 '+ @colname +' FROM tablename'
EXEC(@Str)
SET @Ctr=@Ctr+1
END
November 19, 2009 at 1:08 pm
Thank you. I ran the query and replaced the table name with the actual table I was working with, but my output only says (0 row(s) affected)
So it ran without errors, but did not give me any output.
Any ideas?
November 20, 2009 at 4:27 pm
VRR,
I Took the liberty to upgrade your script a bit...
HBREST
[font="Courier New]
-- 2009-11-21, HBREST, SQLSERVERCENTRAL
-- This script gives you an overview of the column_name, data_type, minimal/maximal length of the contents of that specific column and the first (@TopNo) entries
-- from a given (@Tablename) Table in your database
-- Just set the values for @Tablename and @TopNo as desired and give it a go.
-- User variables
DECLARE @Tablename as varchar(100), @TopNo INT
SET @Tablename = 'DimCustomer'-- Tabel to report
SET @TopNo = 2-- No of values for every column name
-- Body
DECLARE @ColName VARCHAR(20)
DECLARE @Type VARCHAR(20)
DECLARE @Str VARCHAR(8000)
DECLARE @ColCount INT
DECLARE @Ctr INT
CREATE TABLE #TEMP1(RowNo INT IDENTITY(1,1),COLUMN_NAME varchar(20), DATA_Type VARCHAR(20), MINLEN INT, MAXLEN INT, SAMPLEDATA varchar(100))
INSERT INTO #TEMP1 (COLUMN_NAME, DATA_Type, MINLEN, MAXLEN, SAMPLEDATA)
SELECT COLUMN_NAME, DATA_TYPE, 0, 0, REPLICATE('-',100) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @Tablename
SET @ColCount=@@ROWCOUNT
SET @CTR=1
WHILE (@Ctr <= @ColCount)
BEGIN
SELECT @ColName =COLUMN_NAME, @Type = DATA_TYPE
FROM #TEMP1 WHERE RowNo=@Ctr
IF @type <> 'text'
BEGIN
SET @Str='UPDATE #Temp1 SET MINLEN = (SELECT ISNULL(MIN(LEN('+ @colname +')),0) as minlength FROM ' + @Tablename + ') WHERE COLUMN_NAME = ''' + @colname + ''' AND DATA_Type NOT IN (''text'', ''text1'')'
PRINT (@Str)
EXEC(@Str)
SET @Str='UPDATE #Temp1 SET MAXLEN = (SELECT ISNULL(MAX(LEN('+ @colname +')),0) as maxlength FROM ' + @Tablename + ') WHERE COLUMN_NAME = ''' + @colname + ''' AND DATA_Type NOT IN (''text'', ''text1'')'
PRINT (@Str)
EXEC(@Str)
END
SET @Str='INSERT INTO #Temp1(COLUMN_NAME, DATA_TYPE, MINLEN, MAXLEN, SAMPLEDATA) SELECT TOP '+ CAST(@TopNo AS varchar(10)) + ' COLUMN_NAME, DATA_TYPE, MINLEN, MAXLEN, ISNULL(CAST('+ @colname + ' AS VARCHAR(100)),''<NULL>'') FROM ' + @Tablename + ' INNER JOIN #Temp1 ON #Temp1.Column_name LIKE ''' + @ColName + ''' ORDER BY CAST(' + @Tablename + '.' + @ColName + ' AS VARCHAR(1000))'
PRINT (@Str)
EXEC(@Str)
SET @Ctr=@Ctr+1
END
-- Results
SELECT * FROM #TEMP1 ORDER BY COLUMN_NAME, RowNo, SAMPLEDATA
-- Cleanup
DROP TABLE #TEMP1
[/font]
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply