SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 List Column Headers and First Records from Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

redrumrudy
Starting Member

6 Posts

Posted - 05/08/2013 :  13:37:44  Show Profile  Reply with Quote
First of all, hello, I'm pleased to meet all of you.

I'm a newb to creating queries in SQL 2005. I have what may be a unique request. We're in the process of moving from 2005 to MS CRM. We're trying to determine which tables/records to migrate to CRM from 2005. To this end, I've been tasked with supplying the following:

In a single spreadsheet:
1. List of all DB tables;
2. All columns from each table, and;
3. The first data record from each table.

I've managed to extract the first two items in my list without to much trouble. The last item is giving me fits. So if you could help me out, I would appreciate it.

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/08/2013 :  13:41:33  Show Profile  Reply with Quote
SELECT TOP 1 * FROM Table ORDER BY PK

PK is primary key of the table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

redrumrudy
Starting Member

6 Posts

Posted - 05/08/2013 :  14:19:31  Show Profile  Reply with Quote
Thanks for the reply.

Perhaps a stupid question, but, what if some tables don't have a PK?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/08/2013 :  14:32:36  Show Profile  Reply with Quote
Then use the column on which you want the "first record" should based on!

Cheers
MIK
Go to Top of Page

redrumrudy
Starting Member

6 Posts

Posted - 05/08/2013 :  16:39:58  Show Profile  Reply with Quote
Okay, that makes sense.

I think I need to claify my situation a bit. I'm not a DB admin, but rather a Sys Admin. My coding skills aren't the greatest, and I don't have a lot of time to figure out code, but I am getting a crash course in T-SQL/VBasic. So as far as the DB goes, I'm guilty by association. Lol. Any way, I've used the folowing query (which I copy pasted of the I-net) to ger the first two items completed:

declare @xml as xml
declare @max_cols int
declare @i int
declare @query as varchar(max)
declare @query_OA2 as varchar(max)

select @max_cols = MAX(n)
from( select COUNT(name) as n
from sys.columns
group by object_id
)V


select @i = 1, @query_OA2 = ''
while @i <= @max_cols
begin
set @query_OA2 = @query_OA2 +', C.elements.value(''./name['+CAST(@i as varchar(max))+']'',''varchar(max)'') as [Column '+CAST(@i as varchar(max))+']'
set @i = @i+1
end
select @query_OA2 = STUFF(@query_OA2,1,1,'')

select @query = 'select t.name as [table]
,OA2.*
from sys.tables t
outer apply (select (select (select c.name from sys.columns c
where c.object_id = t.object_id
order by c.column_id
for xml path(''''),type) for xml path(''columns''),type) as cols
)OA
outer apply (select '+@query_OA2+'
from OA.cols.nodes(''columns'')C(elements)
)OA2
order by t.name'
exec (@query)
order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, ORDINAL_POSITION;

So I'm guessing that the SELECT TOP 1 * should possibly be coded within, but I'm somewhat unsure where. Possibly withing that final loop?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 05/08/2013 :  18:18:42  Show Profile  Reply with Quote
Another possibility is to use the undocumented function sp_msforeachtable to iterate through the tables.

I have not completely understood what you need to do, but will read through your post and post a sample of how to use sp_msforeachtable later.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 05/08/2013 :  18:34:09  Show Profile  Reply with Quote
I spoke too soon. It is just as messy or messier than the approach you were taking. In any case, here is my half-baked code, for what it is worth.

TO GET THE COLUMN NAMES
CREATE TABLE #A (TABLE_NAME VARCHAR(255), ORDINAL_POSITION INT, COLUMN_NAME VARCHAR(255));
EXEC sp_msforeachtable '
	INSERT INTO #A 
	select 
		TABLE_NAME, 
		row_number() over (partition by TABLE_SCHEMA, TABLE_NAME order by ORDINAL_POSITION) AS COLUMN_NUMBER, 
		COLUMN_NAME 
	from 
		INFORMATION_SCHEMA.COLUMNS 
	WHERE 
		QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?'''

SELECT * FROM #a PIVOT (MAX(COLUMN_NAME) FOR ORDINAL_POSITION IN
([1],[2],[3],[4],[5]))P -- add numbers up to the maximum number of columns

DROP TABLE #A;



TO GET THE FIRST ROW FROM EACH TABLE
(create the temp table with the max number of columns you have in your tables)
CREATE TABLE #B (TABLE_NAME VARCHAR(255), 
	col1 VARCHAR(MAX), col2 VARCHAR(MAX), 
	col3 VARCHAR(MAX), col4 VARCHAR(MAX), col5 VARCHAR(MAX)); 
EXEC sp_msforeachtable N'
	declare @cols varchar(max) = ''(TABLE_NAME '' + 
	(SELECT '',col'' + CAST(row_Number() over (order by ordinal_position) as varchar(32)) 
	FROM INFORMATION_SCHEMA.COLUMNS 
	where QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) =  ''?'' 
	for xml path('''') ) + '')''; 
	declare @sql nvarchar(4000) = ''INSERT INTO #B'' + @cols + '' select top 1  ''''?'''',* from ?  '' ;
	exec sp_executesql @sql'

SELECT * FROM #b;
DROP TABLE #b;
Go to Top of Page

redrumrudy
Starting Member

6 Posts

Posted - 05/08/2013 :  18:47:05  Show Profile  Reply with Quote
James K,

Thanks for the code. I have extracted the column names using the code I posted earlier. I just need the code to get the first row or rows for each table. Heck, it doesn't even have to be the first row, any row will do.

I've looked at what you provided, and I've tried to execute and it's a no-go. I may need to input additional info into the code?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 05/08/2013 :  20:34:48  Show Profile  Reply with Quote
What is the error message you are seeing? The temp tables have to have at least as many columns as the user table table with the largest number of columns.
Go to Top of Page

redrumrudy
Starting Member

6 Posts

Posted - 05/09/2013 :  15:18:23  Show Profile  Reply with Quote
Here I've added the preqewuisite columns:

CREATE TABLE #B (TABLE_NAME VARCHAR(255),
col1 VARCHAR(MAX), col2 VARCHAR(MAX),
col3 VARCHAR(MAX), col4 VARCHAR(MAX),
col5 VARCHAR(MAX), col6 VARCHAR(MAX),
col7 VARCHAR(MAX), col8 VARCHAR(MAX),
col9 VARCHAR(MAX), col10 VARCHAR(MAX),
col11 VARCHAR(MAX), col12 VARCHAR(MAX),
col13 VARCHAR(MAX), col14 VARCHAR(MAX),
col15 VARCHAR(MAX), col16 VARCHAR(MAX),
col17 VARCHAR(MAX), col18 VARCHAR(MAX),
col19 VARCHAR(MAX), col20 VARCHAR(MAX),
col21 VARCHAR(MAX), col22 VARCHAR(MAX),
col23 VARCHAR(MAX), col24 VARCHAR(MAX),
col25 VARCHAR(MAX), col26 VARCHAR(MAX),
col27 VARCHAR(MAX), col28 VARCHAR(MAX),
col29 VARCHAR(MAX), col30 VARCHAR(MAX),
col31 VARCHAR(MAX), col32 VARCHAR(MAX),
col33 VARCHAR(MAX));
EXEC sp_msforeachtable N'
declare @cols varchar(max) = ''(TABLE_NAME '' +
(SELECT '',col'' + CAST(row_Number() over (order by ordinal_position) as varchar(32))
FROM INFORMATION_SCHEMA.COLUMNS
where QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
for xml path('''') ) + '')'';
declare @sql nvarchar(4000) = ''INSERT INTO #B'' + @cols + '' select top 1 ''''?'''',* from ? '' ;
exec sp_executesql @sql'

SELECT * FROM #b;
DROP TABLE #b;

When I execute i get this:

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'for'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@sql".

(0 row(s) affected)

Did I code something incorrectly?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 05/09/2013 :  15:39:29  Show Profile  Reply with Quote
Ah, I forgot that you are on SQL 2005. It does not allow you to declare and assign a variable in one statement. Can you change as shown in red and try one more time? The red stuff is what I changed.

I don't have a SQL 2005 to test, so if this gives you any syntax or other errors, please post back - we can figure this out. The query you posted ran without any issues on my SQL 2008, so hopefully it is only a matter of fixing the syntax:
CREATE TABLE #B (TABLE_NAME VARCHAR(255), 
col1 VARCHAR(MAX), col2 VARCHAR(MAX), 
col3 VARCHAR(MAX), col4 VARCHAR(MAX), 
col5 VARCHAR(MAX), col6 VARCHAR(MAX),
col7 VARCHAR(MAX), col8 VARCHAR(MAX),
col9 VARCHAR(MAX), col10 VARCHAR(MAX),
col11 VARCHAR(MAX), col12 VARCHAR(MAX),
col13 VARCHAR(MAX), col14 VARCHAR(MAX),
col15 VARCHAR(MAX), col16 VARCHAR(MAX),
col17 VARCHAR(MAX), col18 VARCHAR(MAX),
col19 VARCHAR(MAX), col20 VARCHAR(MAX),
col21 VARCHAR(MAX), col22 VARCHAR(MAX),
col23 VARCHAR(MAX), col24 VARCHAR(MAX), 
col25 VARCHAR(MAX), col26 VARCHAR(MAX),
col27 VARCHAR(MAX), col28 VARCHAR(MAX),
col29 VARCHAR(MAX), col30 VARCHAR(MAX),
col31 VARCHAR(MAX), col32 VARCHAR(MAX),
col33 VARCHAR(MAX)); 
EXEC sp_msforeachtable N'
declare @cols varchar(max); set @cols = ''(TABLE_NAME '' + 
(SELECT '',col'' + CAST(row_Number() over (order by ordinal_position) as varchar(32)) 
FROM INFORMATION_SCHEMA.COLUMNS 
where QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?'' 
for xml path('''') ) + '')''; 
declare @sql nvarchar(4000); set @sql = ''INSERT INTO #B'' + @cols + '' select top 1 ''''?'''',* from ? '' ;
exec sp_executesql @sql'

SELECT * FROM #b;
DROP TABLE #b;

Go to Top of Page

redrumrudy
Starting Member

6 Posts

Posted - 05/09/2013 :  16:47:43  Show Profile  Reply with Quote
Most excellent, almost. It extraced 73 out of 82 tables. I'm guessing that those missing tables may not have any actual records or just NULL records. I'll have to check on those.

I also got this error:

Msg 206, Level 16, State 2, Line 1
Operand type clash: image is incompatible with varchar(max)

(73 row(s) affected)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 05/09/2013 :  17:29:45  Show Profile  Reply with Quote
That image data type is a problem. Because it can't be converted to varchar(max). Not sure how to automate that. You might just want to look up all the tables that have at least one column that is of image type and do those manually.

In any case, you won't be able to put the image data type into an excel spreadsheet unless you convert it to an image.

Edited by - James K on 05/09/2013 17:30:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/10/2013 :  00:44:02  Show Profile  Reply with Quote
image you can try to convert to varbinary(max)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.69 seconds. Powered By: Snitz Forums 2000