Quasi Data Dictionary - Select 10 records from each table in Sql Server 1

[BxWill]

Currently reviewing the reports generated from three separate sql server databases that interface each other and do not have a data dictionary.

Therefore, I will have to create a data dictionary on my own.

Objective is to use the sql script below to extract 10 records from each table in each of the sql server
databases and then store the results in a MS Access database to allow me to compare the different fields
across the three databases.

For example, the account number and sub account number is stored in two separate fields in a table for one database but stored
in one field in the other sql server database. So, if I extract 10 records from each table, I will be able to readily determine situations such as this.

select top 10 * from information_schema.columns order by table_name , column_name

Will this accomplish the objective?

If not, what modifications are needed for the sql script?

Any additional insight and assistance is greatly appreciated.

 

[BxWill]

Did construct the following query to enable me to extract just one record from each table in a database.

Of course, I would have to create a select statement for each table in the database.

Is anyone aware of a more efficient method to extract just one record from each table in a database?

select top 1 * from DimCustomer
select top 1 * from DimAccount
select top 1 * from DimCurrency
.
.
.

 

[gmmastros]

try this:

sp_msforeachtable 'Select top 1 * from ?'

Be aware that "under the hood" sp_msforeachtable executes a cursor. This means that the execution of the query will not be as fast as a hard coded list, but unless you have 1,000's of tables you will probably not notice a difference.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk53]

just run that

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;

it will give you all tables and all column names