November 18, 2009 at 9:43 am
I am trying to get some information on all the variables in a table.
I am looking for the min length, max length, and a listing of the unique data in each column.
The code so far is:
select min(len(yearcode)) as minlength from table1
go
select max(len(yearcode)) as maxlength from table1
go
select yearcode from table1 group by yearcode order by yearcode
go
The next set of statements would be:
select min(len(school)) as minlength from table1
go
select max(len(school)) as maxlength from table1
go
select school from table1 group by school order by school
go
Is there a way to get all the columns from the table without having to change the yearcode to the next column in every statement?
Something like a temporary variable where I could put in something like #NewVariable in place of yearcode and school?
I will be looking at these one at a time, but doing a copy and paste for each column is very time consuming.
Thanks
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply