top 100 of every table in one database
-
I have been tasked to transfer Data from one database and covert it suitable for another database.
To start I would like to know the extent ot the tables
I have tried to create a script that reads all the table names (with more than one rowcount), that works, then does a Select Top 100 on the dynamic table that almost works, now ideally I need to dump the test data to excel or into a seperate database to analyse each batch of data. this is a problem as I am selecting into temp tables and haven't got the grasp of copying all 100 line dynamically to an new seperate database with the same column heading, and cannot find a script to modify to dump into excel
Any pointer would be helpful to an untrained beginner, first part of my code below
SET NOCOUNT ON
DECLARE @lngTabCount INTEGER
DECLARE @lngLoopCount INTEGER
DECLARE @strTabName SYSNAME
DECLARE @sql varchar(1000)
Create table #tmp1 (numID INTEGER IDENTITY(1,1),tables sysname)
USE EPDS01
INSERT INTO #tmp1
select [Table] = left(object_name(id), 65)
from dbo.sysindexes
where indid < 2
and objectproperty(id, 'IsUserTable') = 1
and rows > 0
Order by [Table]
SET @lngTabCount = @@ROWCOUNT +1
SET @lngLoopCount = 1
USE EPDS01
WHILE @lngLoopCount <@lngTabcount
BEGIN
SET @strTabName = (SELECT [tables] FROM #tmp1 WHERE numID = @lngLoopCount Group By [tables])
SELECT [tables] FROM #tmp1 WHERE numID = @lngLoopCount Group By [tables]
SELECT @sql = 'SELECT Top 100 * FROM '
SELECT @sql = @sql + @strTabname
Exec ( @sql)
SET @lngLoopCount = @lngLoopCount + 1
END
drop table #tmp1
Regards
Newbie
-
Undocumented, but it should work:
CREATE DATABASE NewDatabase EXEC sp_MSforeachtable 'IF (SELECT COUNT(*) FROM ?)>0 SELECT TOP 100 * INTO NewDatabase.? FROM ?'
Razvan
-
works a treat thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply