Syntax Error - SQL Server Forums

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)

Syntax Error

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-18 : 07:02:59

Hi..when i am trying to run this script its throwing
Incorrect syntax near 'sq'.

this is my script

DECLARE @TableName sysname

DECLARE @SQL nvarchar(max)

DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT values('sq.table name','sq.rows_cont) from ('(SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*) as ROWS_COUNT'+

'FROM ['+ @TableName+'])sq'

EXEC SP_EXECUTESQL @SQL

FETCH NEXT FROM tables_cursor INTO @TableName

END

CLOSE tables_cursor

DEALLOCATE tables_cursor

thanx

yashu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 07:07:11

Oh boy.
Can you please give an example how your statement inside @SQL should look?

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 07:10:04

Haven't you seen my reply here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141523

Why are you using a cursor?

Madhivanan

Failing to plan is Planning to fail

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-18 : 07:13:09

Hi..Fred,

i am looking to get the 2 columns in the record_count table
which insludes all the sys.tables.. table_names and rows as row_count.

thanx

yashu

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-18 : 07:15:24

Madhivanan,
i have to do this for few more tables and finally want to join all the tables into 1 so..for that reason i am using cursor.

thanx

quote:
Originally posted by madhivanan

Haven't you seen my reply here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141523

Why are you using a cursor?

Madhivanan

Failing to plan is Planning to fail

yashu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 07:18:17

See what you get here

Insert into RECORD_COUNT (table name,rows_cont)
select object_name(id),rows from sysindexes where object_name(id)='table_name' and indid<2

SELECT * FROM RECORD_COUNT

Madhivanan

Failing to plan is Planning to fail

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-18 : 07:31:57

Madhivanan,
this is the script which i early used and i am happy with this
(SELECT tab.name AS TABLE_NAME,
ind.rows as RECORD_COUNT
INTO RECORD_COUNT

FROM sys.tables tab INNER JOIN

sys.sysindexes ind ON tab.object_id = ind.id AND ind.indid < 2);

what i am trying is i want to create 3 tables
1)RECORD_COUNT this includes Table_Names and Row_count all the sys.tables.
2)INSERT_DATE_COUNT this includes table_names and count for INSERT_DATE>= getdate(),this insert date column originally staying in that perticular table ex: there 100 table in Table_Names column and i want to get no_of_records inserted to that individual table >=getdate() based on that insert_date column .insert_date column exists for all the tables not considering this insert date from sys.tables, i want this INSERT_DATEfrom original data from the tables.
3)UPDATE_DATE_COUNT i have to repeat the same for this aswell.

finall i want to write SP whih will get all the columns in one table

TABLE_NAME,ROW_COUNT,NO_OF_INSERTS and NO_OF_UPDATES

I am writing the below script for getting the above result

ill add the develop this lateron but iam getting that syntax error at 'sq' when executing upto wat i have written.
DECLARE @TableName sysname

DECLARE @SQL nvarchar(max)

DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT values('sq.table name','sq.rows_cont) from ('(SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*) as ROWS_COUNT'+

'FROM ['+ @TableName+'])sq'

EXEC SP_EXECUTESQL @SQL

FETCH NEXT FROM tables_cursor INTO @TableName

END

CLOSE tables_cursor

DEALLOCATE tables_cursor

yashu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-30 : 03:40:59

Print @SQL and post the result

Madhivanan

Failing to plan is Planning to fail

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-30 : 04:14:38

hello madhavan,

this problem was already solved..

no i am facing with different issue whic i mentioned in new topic .

thanx for ur help..

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources