1

I have an SQL script that outputs these, given a table name:

  • the column names in the table
  • the datatypes for those columns
  • the column allows NULL or not
  • the column has unique constraint or not.

Like this:

column | datatype | nullable | unique
--------------------------------------
       |          |          |
       |          |          |
       |          |          |

Then I save the result of the script into a csv file, and run a python script to do something else with the result1. Now, what I want to do is automate the process. For about 2000 tables in the database, it is painstakingly clumsy to do the running-sql-script then saving-to-csv-file and then running-python-script cycle for each of those 2000 tables.

Is there any way to automate this in SSMS. I am using SSMS 2014. I have looked here. Though that is related, that isn't actually what I want.

1. I use the python script to create a C# domain class to be used by entity framework.

Community
  • 1
  • 1
Sнаđошƒаӽ
  • 16,753
  • 12
  • 73
  • 90
  • You can use `BCP` (like [in this question](http://dba.stackexchange.com/questions/23566/writing-select-result-to-a-csv-file)) to output to CSV from your query results, then just use a cursor to iterate through whichever tables you need to do this for. – 3N1GM4 Dec 08 '16 at 10:27
  • 1
    You build a reusable [Integration Servies](https://msdn.microsoft.com/en-us/library/ms141026.aspx?f=255&MSPPError=-2147217396) package. Among others things, the package could kick-off your Python script. – David Rushton Dec 08 '16 at 10:47
  • @destination-data From the link you gave I came to know that that is a *SQL Server 2016 and later* feature, but I am using SQL Server 2014. Thanks for the info though. – Sнаđошƒаӽ Jan 14 '17 at 16:49
  • @destination-data I am using SQL Server 2016 now, so I will give what you said a try. – Sнаđошƒаӽ May 07 '18 at 17:54

1 Answers1

0

You can use sysobjects cursor to collect the id of the table and run bcp utility to export the results to CSV table. Here is the script that i write for this job.

DECLARE @TableID int

DECLARE @TableName varchar(100)

DECLARE @sql_command varchar(4000)

DECLARE @bulk_cmd varchar(4000)

DECLARE @DatabaseName varchar(100)  ='AdventureWorks2012'

DECLARE @SQLServerName varchar(100)  ='CEMUNEY\S12'

DECLARE Cursor_Line CURSOR FOR
SELECT id,name FROM sys.sysobjects where type='U'

OPEN Cursor_Line
FETCH NEXT FROM Cursor_Line INTO @TableID, @TableName


WHILE @@FETCH_STATUS = 0

BEGIN


    SET @sql_command = N'SELECT [column] = cols.name ,[datatype] = typeuser.name ,is_nullable ,is_identity FROM @DatabaseName.sys.columns cols INNER JOIN @DatabaseName.sys.systypes datatypes ON cols.user_type_id = datatypes.xusertype LEFT OUTER JOIN @DatabaseName.sys.systypes typeuser ON  datatypes.xtype = typeuser.xusertype where object_id =' + STR(@TableID)


    SET @sql_command = REPLACE(@sql_command,  '@DatabaseName', @DatabaseName)

    set @bulk_cmd = 'SQLCMD -S @SQLServerName -W -E -q  "set nocount on;' + @sql_command +  '" -s "," -o "D:\' +  @TableName + '.csv"'

    SET @bulk_cmd = REPLACE(@bulk_cmd,  '@SQLServerName', @SQLServerName)


    exec xp_cmdshell @bulk_cmd

FETCH NEXT FROM Cursor_Line INTO @TableID, @TableName


END

CLOSE Cursor_Line
DEALLOCATE Cursor_Line
Cem Üney
  • 25
  • 7