List Column Headers and First Records from Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

List Column Headers and First Records from Tables

Author

Topic

redrumrudy
Starting Member

6 Posts

Posted - 2013-05-08 : 13:37:44

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

52326 Posts

Posted - 2013-05-08 : 13:41:33

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

redrumrudy
Starting Member

6 Posts

Posted - 2013-05-08 : 14:19:31

Thanks for the reply.

Perhaps a stupid question, but, what if some tables don't have a PK?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-08 : 14:32:36

Then use the column on which you want the "first record" should based on!

Cheers
MIK

redrumrudy
Starting Member

6 Posts

Posted - 2013-05-08 : 16:39:58

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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-08 : 18:18:42

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-08 : 18:34:09

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;

redrumrudy
Starting Member

6 Posts

Posted - 2013-05-08 : 18:47:05

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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-08 : 20:34:48

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.

redrumrudy
Starting Member

6 Posts

Posted - 2013-05-09 : 15:18:23

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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-09 : 15:39:29

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;

redrumrudy
Starting Member

6 Posts

Posted - 2013-05-09 : 16:47:43

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)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-09 : 17:29:45

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-10 : 00:44:02

image you can try to convert to varbinary(max)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources