How do I fix incorrect syntax near SQL Server?

To fix incorrect syntax near SQL Server, you may need to set the compatibility level of the current database to a higher value to enable this feature. While it's certainly possible that someone changed your stored procedure and broke it in the process, the simplest explanation for something like this breaking is a change in the input data that causes it to break. In the comments, a number of people tried your stored procedure in various versions of SQL Server, and it ran successfully. This makes it even more likely that the problem is a change in the data being provided to the stored procedure. If you can think of a table that's using a data type that none of your tables has used before, that could cause your procedure to start running a branch that it had never needed to run before, causing an error. You can troubleshoot this using the following steps: You've already got a statement that prints out the dynamic SQL you execute to your "Messages" window in SSMS before it executes it. Look at the output in that window carefully. Are any tables processed, or are you failing on the very first table? If you are failing on the very first table, before @script_sql is even printed once, then try running the stored procedure manually. Using SSMS, in the Object Explorer window: open a Database Engine connection to your server; expand your server; then Databases; then your database; then Programmability; then Stored Procedures. Right-click on your stored procedure, and select "Script Stored Procedure As", "CREATE To", "New Query Editor Window". In the query editor window, modify the code that creates your stored procedure so you can run the code directly. remove or comment out the CREATE PROCEDURE line; change the parameter list to DECLARE statements; and comment out or remove the AS. Figure out the first table name your script is trying to process. Manually set @nom_table to this table name in your DECLARE statement (or in a SET statement right afterward). Now, run the modified code from your stored procedure. See exactly where it stops (add additional PRINT statements if necessary to determine exactly where it stops). If you get to the point where @script_sql is printed out, try copying the statement and running that manually, in a new query window. If that fails, look at the SQL statement carefully. Get the length of @script_sql, and compare that with what was output; beyond a certain length, PRINT will truncate your value (so you wouldn't be getting all of @script_sql in the "Messages" window). You can overcome this by printing substrings of @script_sql If you don't see any obvious errors in the statement, try running parts of it only. Can you create the table with just the first column? just the first two? etc. As you include additional columns, you will eventually find one or more that are causing issues. For each test, start from the full code in @script_sql, to minimize the likelihood of introducing errors by repeated cutting and pasting. One of the current answers points out a couple of potential issues in your code; I'll point out one more. I don't think the line:

set  @script_sql= case
                    when left(@script_sql,1)=','
                      then left(@script_sql, LEN(@script_sql) -1)
                    else   @script_sql
                  end  + ') '

is working as you'd expect it to. I believe it's intended to strip a trailing comma from the SQL statement before adding the closing parenthesis. However, it's actually checking if the first character in @script_sql is a comma, not the last. Try this instead:

set  @script_sql= case
                    when right(@script_sql,1)=','
                      then left(@script_sql, LEN(@script_sql) -1)
                    else   @script_sql
                  end  + ') '

Finally - you can get an idea of whether anyone has changed your procedure from SSMS. If you don't still have everything expanded, follow the directions from step 2 above to expand your server and database to the point where you see the "Stored Procedures" folder. Left-click on the folder, then go to the "Windows" menu and choose "Object Explorer Details". You'll get a view of your stored procedure that includes "Create Date" and "Date Last Modified" (if you don't see those, right-click on the headers for the columns you do see, and select those columns so you can see them.) Check the create date and last modified date for the procedure; has it been changed recently? If so, then hopefully you have an old version of the stored procedure in a text or .sql file somewhere; or, you have an old backup of your database (you can restore this under a different name, and script out the stored procedure from the backup. You can then remove the old restored DB, and try the older version of the stored procedure, to see if it works). You should also be using NVARCHAR for all of this instead of VARCHAR. And change the declaration of @code_colonne VARCHAR(1000) to be NVARCHAR(MAX). Something might be being silently truncated, or how code_colonne is being built, such as a blank or still NA (instead of a NULL) for Longest causing the resulting type to be either CHAR() or CHAR(NA). You could also run a Profiler trace, to see what's going on during the procedure execution.