outputting results to different file names

  • 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

     

  • 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