Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm having a huge database over 100GBs and I need to burn an application (for demonstration purposes) on CD or DVD which uses this huge database... is there a way how to leave for example only first 100 records IN ALL TABLES ?

Thanks in advance.

share|improve this question

closed as off topic by SmallClanger, womble, Wesley, Michael Hampton, Ward Oct 8 '12 at 3:31

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers 2

Create copy of database and copy necessary data to it using constructions like:

exec sp_MSforeachtable 'SELECT TOP 100 * INTO [newdb].[?] FROM [olddb].[?]';

share|improve this answer
    
Thanks! Ok, this is possible solution. But the fact is, that I don't know every table I need. It is very complex database and I'm using many views, which are bound to many "things" I don't know. And it's not possible to "copy" every table using TOP 100 because there are thousands of tables in this db. So there is nothing what I can do to copy the db as it is but only with some data? –  user116812 Apr 8 '12 at 8:58

I sometimes have to do something similar, usually when I take a production database into dev or test.

In most cases, I try to identify if there is a few tables that take up almost all of the space - that is usually the case.

Run this script:

DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count BIGINT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
--make sure the object exists before calling sp_spaceused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table

I did not write it myself, but have it in a textfile, and I ashamed I no longer remember what source to credit... You can do the same thing in Management Studio btw (add a "Size" column to details view).

Now you know what tables take up the most space, and can deal with them indivdually.

EDIT: Found source: http://www.howtogeek.com/howto/database/determine-size-of-a-table-in-sql-server/

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.