May 11, 2004 at 9:26 am
Hi there,
I have a query:
Declare @TableName Varchar(1000)
DECLARE TOP10DATA_Cursor CURSOR FOR
Select Distinct SO.Name "Tables"
From SysObjects SO
Where (SO.id Not In (Select rkeyid From SysReferences) And SO.id Not In (Select fkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name
OPEN TOP10DATA_Cursor
DECLARE @sql varchar(1000)
FETCH NEXT FROM TOP10DATA_Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sql = 'Select top 5 * from [' + @TableName + ']'
print(@TableName)
exec(@SQL)
FETCH NEXT FROM TOP10DATA_Cursor INTO @TableName
END
Close TOP10DATA_Cursor
Deallocate TOP10DATA_Cursor
Pretty basic, just goes thru and gets the top 5 records for every table (manager just needs to see whats there for a conversion). Thru qa I can send the output to a file but it doesn't really import into excel well since all the columns are different lengths.
Is there anyway that I can output the results of the above to an individual file name per table. there are about 50 tables or so, and while I could do it by hand I would rather not. I thought there was a way using xp_cmdshell but I poked around and couldn't find anything in particular.
Thanks,
Chris
May 12, 2004 at 5:14 am
Declare @TableName Varchar(1000)
DECLARE TOP10DATA_Cursor CURSOR FOR
Select Distinct SO.Name "Tables"
From SysObjects SO
Where (SO.id Not In (Select rkeyid From SysReferences) And SO.id Not In (Select fkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name
OPEN TOP10DATA_Cursor
DECLARE @sql varchar(1000)
FETCH NEXT FROM TOP10DATA_Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sql = 'Select top 5 * from '+db_name()+'..[' + @TableName + ']'
print(@TableName)
set @sql = 'exec master.dbo.xp_cmdshell ''bcp "'+ @sql + '" queryout c:\temp\' + @TableName + '.txt -c'''
exec(@SQL)
FETCH NEXT FROM TOP10DATA_Cursor INTO @TableName
END
Close TOP10DATA_Cursor
Deallocate TOP10DATA_Cursor
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply