October 30, 2005 at 8:43 pm
First ,I need to query all the tables those includes string fields .
Then I want to search these tables,and iterate each string field to get the total string length.So the
table name and filed name for the select command need to be known dynamically.
Is it possible to do like this with sql script in SQL server?
Thanks and best regards !
October 30, 2005 at 10:08 pm
Looks like you need Dynamic SQL to achieve what you want
October 31, 2005 at 1:59 am
But I wonder if I can use 2 Cursors to make a dynamic "select" in QueryAnalyzer.
Because I do not want write any program except for SQL script.
Is it possible ?
November 1, 2005 at 3:14 am
post your table structre and more specific requirement and we will be able to help you better
November 1, 2005 at 4:01 am
There are 100 tables in my database . A view, named SV,is already created to record map information between table and columns. The view has 3 fields : Table_Name,Column_Name,Column_Type
For some reason ,I need to know the total string length of the string-typed fields. For example in table t1, there are 10 fields, 2 of them are string typed: f11,f12.
"select Sum(Len(f1)) + Sum(Len(f2) ) from dbo.v_OSDD_Channel_Info"
I don't want to write a similar script for 100 times. So I want to :
First get all tables which has string-typed fields by querying the view:
"select distinct Table_Name from SV where Column_Type like 'varchar%'"
Second I want to use a Cursor to iterate each table,then all all string fields by the query.
"select column_Name from SV where Column_Type like 'varchar%' and Table_Name = ?".
Here, I don't know how to use a cursor variable as a table name in SQL script.
Furthermore, how to use a cursor variable as a field name in SQL script.
I don't know if it is possible to do like this in SQL scrpt. Currently ,I use c# to achive this requirement.
November 1, 2005 at 6:34 am
if you need to know the maximum possible length of all the text fields, you could use this, whihc does not use a cursor:
select sysobjects.name as TableName,
sum(syscolumns.length) as LenOfTextFields
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where TYPE_NAME(syscolumns.xtype) in('CHAR','VARCHAR','NCHAR','NVARCHAR')
group by sysobjects.name
if you need to know the max length of the actual data in the rows, then i think you'll need to use a cursor thru each table.
a variation in case you need to know the column names:
select sysobjects.name as TableName,
syscolumns.name as columnname from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where TYPE_NAME(syscolumns.xtype) in('CHAR','VARCHAR','NCHAR','NVARCHAR')
and sysobjects.xtype in('U','V')
Lowell
November 1, 2005 at 9:04 am
DECLARE @tablename sysname, @sql nvarchar(4000)
DECLARE tabcur CURSOR FAST_FORWARD
FOR SELECT DISTINCT Table_Name FROM SV WHERE Column_Type LIKE '%char'
OPEN tabcur
FETCH NEXT FROM tabcur INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = NULL
SELECT @sql=COALESCE(@sql+'+SUM(LEN('+[Column_Name]+'))','SUM(LEN('+[Column_Name]+'))')
FROM SV
WHERE Table_Name=@tablename
AND Column_Type LIKE '%char'
SET @sql='SELECT '''+@tablename+''','+@sql+' FROM ['+@tablename+']'
EXEC(@sql)
FETCH NEXT FROM tabcur INTO @tablename
END
CLOSE tabcur
DEALLOCATE tabcur
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply