needed help on special kind of query which extracts data

• Amol Verma

  Grasshopper

  Points: 17

  More actions

  November 26, 2009 at 10:04 pm

  #140217

  Hi,

  can anyone provide me a solution or a tool or a script that can help me in achieving my following goal

  i want to fetch first few rows of every table from my database e.g. say i have 50 tables with 100 rows in each table. now i want to select first 5 rows or n rows from each 50 tables but with a single script or some tool or some method..

  it will be a kind of small backup of whole database with first few rows and it can be used as dummy entries for test database.

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  November 27, 2009 at 12:52 am

  #1084795

  Try this....

  CREATE TABLE #TEMP (Rid int identity , NAME VARCHAR(1000))

  INSERT INTO #TEMP

  exec SP_MSforeachDB 'USE ? Select TABLE_CATALOG+''..''+TABLE_NAME From Information_schema.Tables'

  SET NOCOUNT ON

  DECLARE @Cnt INT ,@I INT ,@Name VARCHAR(1000)

  SET @I=1

  SELECT @Cnt=Count(*) FROM #TEMP

  WHILE @i<=@Cnt

  BEGIN

  SELECT @Name=Name FROM #TEMP WHERE Rid=@i

  EXEC ('Select Top 1 * From '+ @Name)

  SET @I=@I+1

  END

  Thanks
  Parthi