How to run sql query using python and pandas

Question

I habe just created a query as the below one:

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

as this query works fine when execute in the sql but When I tired to execute using python I got an error.

So Now I need to run this query using python as the below code:

from database import connec
import pandas as pd

def delete_all_tables_from_db():
    delete_all_tables_query = "/* Drop all non-system stored procs */ " \
                              + "DECLARE @name VARCHAR(128) " \
    + "DECLARE @SQL VARCHAR(254) " \
    + "SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) " \
    + "WHILE @name is not null " \
    + "BEGIN " \
    + "SELECT @SQL = 'DROP PROCEDURE [dbo].[N' + QUOTENAME(@name) +N']' " \
    + "EXEC (@SQL) " \
    + "PRINT 'Dropped Procedure: ' + @name " \
    + "SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) " \
    + "END " \
    + "GO " \
    + "/* Drop all views */ " \
    + "DECLARE @name VARCHAR(128) " \
    + "DECLARE @SQL VARCHAR(254) " \
    + "SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) " \
    + "WHILE @name IS NOT NULL " \
    + "BEGIN " \
    + "SELECT @SQL = 'DROP VIEW [dbo].[N' + QUOTENAME(@name) +N']' " \
    + "EXEC (@SQL) " \
    + "PRINT 'Dropped View: ' + @name " \
    + "SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) " \
    + "END " \
    + "GO " \
    + "/* Drop all functions */ " \
    + "DECLARE @name VARCHAR(128) " \
    + "DECLARE @SQL VARCHAR(254) " \
    + "SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name]) " \
    + "WHILE @name IS NOT NULL " \
    + "BEGIN " \
    + "SELECT @SQL = 'DROP FUNCTION [dbo].[N' + QUOTENAME(@name) +N']' " \
    + "EXEC (@SQL) " \
    + "PRINT 'Dropped Function: ' + @name " \
    + "SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) " \
    + "END " \
    + "GO " \
    + "/* Drop all Foreign Key constraints */ " \
    + "DECLARE @name VARCHAR(128) " \
    + "DECLARE @constraint VARCHAR(254) " \
    + "DECLARE @SQL VARCHAR(254) " \
    + "SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) " \
    + "WHILE @name is not null " \
    + "BEGIN " \
    + "SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) " \
    + "WHILE @constraint IS NOT NULL " \
    + "BEGIN " \
    + "SELECT @SQL = 'ALTER TABLE [dbo].[' + QUOTENAME(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' " \
    + "EXEC (@SQL) " \
    + "PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name " \
    + "SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) " \
    + "END " \
    + "SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) " \
    + "END " \
    + "GO " \
    + "/* Drop all Primary Key constraints */ " \
    + "DECLARE @name VARCHAR(128) " \
    + "DECLARE @constraint VARCHAR(254) " \
    + "DECLARE @SQL VARCHAR(254) " \
    + "SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) " \
    + "WHILE @name IS NOT NULL " \
    + "BEGIN " \
    + "SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) " \
    + "WHILE @constraint is not null " \
    + "BEGIN " \
    + "SELECT @SQL = 'ALTER TABLE [dbo].[' + QUOTENAME(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']' " \
    + "EXEC (@SQL) " \
    + "PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name " \
    + "SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) " \
    + "END " \
    + "SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) " \
    + "END " \
    + "GO " \
    + "/* Drop all tables */ " \
    + "DECLARE @name VARCHAR(128) " \
    + "DECLARE @SQL VARCHAR(254) " \
    + "SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) " \
    + "WHILE @name IS NOT NULL " \
    + "BEGIN " \
    + "SELECT @SQL = 'DROP TABLE [dbo].[N' + QUOTENAME(@name) +N']' " \
    + "EXEC (@SQL) " \
    + "PRINT 'Dropped Table: ' + @name " \
    + "SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) " \
    + "END " \
    + "GO"

    delete_all_tables_df = pd.read_sql(delete_all_tables_query, connec.engine)
    connec.engine.execute(delete_all_tables_df)

delete_all_tables_from_db()

and this error I found:

Traceback (most recent call last):
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1245, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@constraint' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:/Users/haroo501/PycharmProjects/ToolUpdated/database/delete_all_tables_from_db.py", line 93, in <module>
    delete_all_tables_from_db()
  File "C:/Users/haroo501/PycharmProjects/ToolUpdated/database/delete_all_tables_from_db.py", line 90, in delete_all_tables_from_db
    delete_all_tables_df = pd.read_sql(delete_all_tables_query, connec.engine)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\io\sql.py", line 432, in read_sql
    return pandas_sql.read_query(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\io\sql.py", line 1218, in read_query
    result = self.execute(*args)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\io\sql.py", line 1087, in execute
    return self.connectable.execute(*args, **kwargs)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2182, in execute
    return connection.execute(statement, *multiparams, **params)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 976, in execute
    return self._execute_text(object_, multiparams, params)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1143, in _execute_text
    ret = self._execute_context(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1249, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1245, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@constraint' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure. (134); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'GO'. (102)")
[SQL: /* Drop all non-system stored procs */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Procedure: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) END GO /* Drop all views */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped View: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) END GO /* Drop all functions */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Function: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) END GO /* Drop all Foreign Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) WHILE @name is not null BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint IS NOT NULL BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' EXEC (@SQL) PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) END GO /* Drop all Primary Key constraints */ DECLARE @name VARCHAR(128) DECLARE @constraint VARCHAR(254) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) WHILE @name IS NOT NULL BEGIN SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) WHILE @constraint is not null BEGIN SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']' EXEC (@SQL) PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) END SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) END GO /* Drop all tables */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Table: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) END GO]
(Background on this error at: http://sqlalche.me/e/f405)

So How to use long query as to execute it with python

FYI `'...[' + RTRIM(@name) +']...'` does not make injection safe code. YOu could be using `N'...' + QUOTENAME(@name) + N'...'` — Thom A, Feb 24 '20 at 10:17
[Dos and Don'ts of Dynamic SQL](https://www.sqlservercentral.com/articles/dos-and-donts-of-dynamic-sql) — Thom A, Feb 24 '20 at 10:19
also refer https://stackoverflow.com/questions/25680812/incorrect-syntax-near-go — Shijith, Feb 24 '20 at 10:23
@Shijith related to my code how Can I solve this? I still stucking in this error the query qorks fine on the managment studio but doesn't work when using python — Mahmoud Al-Haroon, Feb 24 '20 at 10:37
https://stackoverflow.com/questions/43625013/incorrect-syntax-near-go-in-sql — Shijith, Feb 24 '20 at 10:43
My comments weren't a solution to your problem, they were an advisement to fix the injection issue. I did mention that in the comment. :) — Thom A, Feb 24 '20 at 10:46
@Larnu thanks a lot for your support, even there's another way to delete all tables in `DB` using python directly with using query?... it will really solve me alot of problems :) — Mahmoud Al-Haroon, Feb 24 '20 at 10:48
What do you mean, "delete all tables"? In your query, you're performing a `DROP` against multiple types of object, not just `TABLE`s. It would seem far quicker (and easier) to just `DROP` the database (and recreate it). — Thom A, Feb 24 '20 at 10:53
@Larnu I think the deleting the database is a very good idea :) — Mahmoud Al-Haroon, Feb 24 '20 at 10:57

score 1 · Answer 1 · answered Feb 24 '20 at 12:03

Your code is creating dynamic sql by string concatenation, but it seems to me that you are missing all the newlines, resulting in a single line of T-SQL. This can give syntax errors if not correctly separated.

There are two ways you can fix this:

1) Use newlines characters in your string in order to create a string that is equal to the original syntax. Example:

delete_all_tables_query = "/* Drop all non-system stored procs */ \n" \
                              + "DECLARE @name VARCHAR(128) \n" \
    + "DECLARE @SQL VARCHAR(254) \n" \
    + "SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) \n" \
    + "WHILE @name is not null \n" \
    + "BEGIN \n" \
    + "SELECT @SQL = 'DROP PROCEDURE [dbo].[N' + QUOTENAME(@name) +N']' \n" \
    + "EXEC (@SQL) \n" \
[etc...]

2) Create a stored procedure to run your code and invoke it in python code. Example:

CREATE PROCEDURE EmptyDatabase
AS
BEGIN
    /* Drop all non-system stored procs */
    DECLARE @name VARCHAR(128)
    DECLARE @SQL VARCHAR(254)
    DECLARE @constraint VARCHAR(254)

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

    WHILE @name is not null
    BEGIN
        SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
        EXEC (@SQL)
        PRINT 'Dropped Procedure: ' + @name
        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
    END


    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

    WHILE @name IS NOT NULL
    BEGIN
        SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
        EXEC (@SQL)
        PRINT 'Dropped View: ' + @name
        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
    END


    /* Drop all functions */
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

    WHILE @name IS NOT NULL
    BEGIN
        SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
        EXEC (@SQL)
        PRINT 'Dropped Function: ' + @name
        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
    END


    /* Drop all Foreign Key constraints */

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

    WHILE @name is not null
    BEGIN
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
        WHILE @constraint IS NOT NULL
        BEGIN
            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
            EXEC (@SQL)
            PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
        END
    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
    END


    /* Drop all Primary Key constraints */
    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

    WHILE @name IS NOT NULL
    BEGIN
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
        WHILE @constraint is not null
        BEGIN
            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
            EXEC (@SQL)
            PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
        END
    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
    END


    /* Drop all tables */
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

    WHILE @name IS NOT NULL
    BEGIN
        SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
        EXEC (@SQL)
        PRINT 'Dropped Table: ' + @name
        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
    END

END

then:

def delete_all_tables_from_db():
    delete_all_tables_query = 'exec EmptyDatabase'
    connec.engine.execute(delete_all_tables_query)

delete_all_tables_from_db()

Please note that your SQL syntax does not actually return any recordset, so you have nothing to be read in both solutions, just execute the sql string.

As someone already commented, you could also drop the database and recreate it, assuming the application has necessary permissions.

I wonder what use has all this, though, since a database is used to persist data, and code to emptying/dropping it is only to be used once :) The only thing I can think of is a Python based Sql Server Management Studio XD

I appretiate your help alot and thank for your help so regarding for deleting the database I tried it by using `DROP DATABASE [toolDB]` but I got an error with `DROP DATABASE statement cannot be used inside a user transaction.`So do you have any Idea for how to assume the application for user permission, this solution seems to be better, I am glad for heling me :).. — Mahmoud Al-Haroon, Feb 24 '20 at 12:27
what do you mean that "Python based Sql Server Management Studio" you mean the best SQL Enviroment is SQL Server Managment Studio?? Right or I can use another sql enviroment? — Mahmoud Al-Haroon, Feb 24 '20 at 12:33

How to run sql query using python and pandas

1 Answers1