Here is a solution based on cursors and dynamic SQL. I've includes the table schema and column names in the final result as well, though the question just asks fro table name, data type, and sample data.
Also, I wasn't sure if you wanted three rows of sample data per table/column, or if you wanted one row per table/column with three columns of sample data. I went with the former, please let me know if you wanted the later. I did include a "No data" indicator for tables that don't have any sample data.
Tested on SQL Server 2005, but I think it should work with 2000 as well.
Create Table #results
(id Integer Not Null Identity(1, 1)
,table_schema nVarChar(128) Not Null
,table_name nVarChar(128) Not Null
,column_name nVarChar(128) Not Null
,data_type nVarChar(128) Not Null
,sample_data nVarChar(max) Null);
Declare @table_name nVarChar(128)
,@table_schema nVarChar(128)
,@column_name nVarChar(128)
,@data_type nVarChar(128)
,@sql nVarChar(max)
,@inserted Integer;
Declare rs Cursor Local Forward_Only Static Read_Only
For Select
col.table_schema
,col.table_name
,col.column_name
,col.data_type
From INFORMATION_SCHEMA.COLUMNS col
Order By col.TABLE_CATALOG
,col.TABLE_SCHEMA
,col.TABLE_NAME
,col.ORDINAL_POSITION
Open rs;
Fetch Next From rs Into @table_schema, @table_name, @column_name, @data_Type;
While @@Fetch_Status = 0 Begin;
Set @table_schema = QuoteName(@table_schema);
Set @table_name = QuoteName(@table_name);
Set @column_name = QuoteName(@column_name);
Set @sql = N'
Insert Into #results
(table_schema
,table_name
,column_name
,data_type
,sample_data)
Select Top 3 ' + QuoteName(@table_schema, '''') + N'
,' + QuoteName(@table_name, '''') + N'
,' + QuoteName(@column_name, '''') + N'
,' + QuoteName(@data_type, '''') + N'
,' + @column_name + N'
From ' + @table_schema + N'.' + @table_name;
Exec (@sql);
Select @inserted = count(*)
From #results
Where table_schema = @table_schema
And table_name = @table_name
And column_name = @column_name;
If @inserted = 0
Insert Into #results (table_schema, table_name, column_name, data_type, sample_data)
Values (@table_schema, @table_name, @column_name, @data_type, ' -- No Data -- ');
Fetch Next From rs Into @table_schema, @table_name, @column_name, @data_Type;
End;
Close rs;
Deallocate rs;
-- Probably should include the schema and column name too:
Select table_schema, table_name, column_name, data_type, sample_data
From #results
Order by [id];
-- But this is what the question asked for:
-- Select table_name, data_type, sample_data
-- From #results
-- Order by [id];
Drop Table #results;
There are probably more elegant solutions available, but this should get you started, I think. Good luck!