November 26, 2008 at 6:03 am
I have inherited an absolutely awful database with over 1,000 badly designed tables. I want to go through it resetting for example column sizes and datatypes. I only need a simple query like
select top 10 len(fieldname) as length, count(len(fieldname)) as Num
from [tablename]
Group by len(fieldname)
Order by Num desc
but I rather than do that for every field for all 1,000+ tables I would like a query that accepts a tablename as a parameter and automatically runs the query for every column. I presume this could be done by referencing the metadata but have no idea how to do it.
Does anyone have a bright idea?
November 26, 2008 at 6:10 am
Could you be more specific? Providing a table with definition and exactly what you want to return from that table would help. See the link in my signature for tips on how to provide table definition / sample data.
November 26, 2008 at 6:19 am
Hi Seth, thanks for responding.
The essence of the problem in NOT being specific. It needs to run for every column.
November 26, 2008 at 6:27 am
I get that. However, if you can show me a specific example of what you want to return, I can understand what you're trying to accomplish. Different metadata is held in different places, and depending on which pieces you need to see specifically, the query will look different.
November 26, 2008 at 7:32 am
Hi again Seth
I don't know if this ackground helps. For every string fieldname in tablename I am checking to see how large the entries are. This is what I have been using manually and would like to generalise:
select top 10 len(fieldname) as length, count(len(fieldname)) as Num
from [tablename]
Group by len(fieldname)
Order by Num desc
The reason for this is that there is no data validation in the application populating the tables and I need to determine how large the fields really should be. The design of the tables include things like an index on a column of type nvarchar(max) that should never have more than two characters in it.
November 26, 2008 at 8:45 am
This can be done using some dynamic SQL and querying the system tables or INFORMATION_SCHEMA views...sample code attached:
-- setup data
USE tempdb;
IF OBJECT_ID('dbo.TestTab1') IS NOT NULL
DROP TABLE dbo.TestTab1;
IF OBJECT_ID('dbo.TestTab2') IS NOT NULL
DROP TABLE dbo.TestTab2;
IF OBJECT_ID('dbo.TestTab3') IS NOT NULL
DROP TABLE dbo.TestTab3;
CREATE TABLE dbo.TestTab1(Id1 int, Val1 varchar(30));
CREATE TABLE dbo.TestTab2(Id2 int, Val2 varchar(50));
CREATE TABLE dbo.TestTab3(Id1 int, Id2 int,Val1 varchar(30),Val2 varchar(50));
INSERT dbo.TestTab1(Id1,Val1)
SELECT 100,REPLICATE('X',30)
UNION ALL
SELECT 101,REPLICATE('Y',30)
UNION ALL
SELECT 102,REPLICATE('Z',30)
INSERT dbo.TestTab2(Id2,Val2)
SELECT 500,REPLICATE('A',50)
UNION ALL
SELECT 501,REPLICATE('B',50)
UNION ALL
SELECT 502,REPLICATE('C',50)
INSERT dbo.TestTab3(Id1,Id2,Val1,Val2)
SELECT T1.Id1,T2.Id2,T1.Val1,T2.Val2 FROM dbo.TestTab1 T1 CROSS JOIN dbo.TestTab2 T2
SELECT * FROM dbo.TestTab1
SELECT * FROM dbo.TestTab2
SELECT * FROM dbo.TestTab3
GO
DECLARE @TableName sysname,@Sql nvarchar(max)
DECLARE @ColList TABLE(ID int IDENTITY(1,1),ColName sysname,SqlStr nvarchar(max))
SET @TableName = 'TestTab3'
INSERT @ColList(ColName)
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
-- indenting string for readability
UPDATE @ColList SET SqlStr = 'SELECT TOP 10 '+QUOTENAME(@TableName,'''')+'as TabName,'+QUOTENAME(ColName,'''')+' as ColName, LEN('+ColName+') as ColLength,
COUNT(LEN('+ColName+')) as Num FROM '+@TableName+'
GROUP BY LEN('+ColName+')
ORDER BY Num DESC'
SELECT * FROM @ColList
SET @sql = ''
SELECT @sql = @sql + SqlStr+Char(10)+Char(13) FROM @ColList
PRINT @sql
EXECUTE(@Sql)
November 26, 2008 at 9:43 am
Thanks! You are without doubt a genius.
I had a slight problem because some tables use NTEXT or had spaces in the table name but your solution has saved me hours.
Thanks again
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply