-2

How to insert a sql script in a table column?

I have a table column which has ntext datatype. I will have to insert the whole function or stored procedure in the column.

Giving an example : sp_helptext 'sp_TestProcedure' will return the complete syntax of a stored procedure. How to populate the stored procedure script in a Table.

I can change the data type either ntext or nvarchar(max). Actual question is , how to insert the script in a column ?

This is not an insert of stored procedure result. This script which i am looking for is to insert the actual stored procedure (or) function (or) view in a table

goofyui
  • 3,362
  • 20
  • 72
  • 128
  • 7
    `ntext`??? No. `nvarchar(max)`. – Gordon Linoff Nov 26 '18 at 21:17
  • 1
    Why would you *want* to do this? You can't be wanting to execute the SQL from there; that would be injection paradise. So, what are you *really* trying to achieve here? – Thom A Nov 26 '18 at 21:20
  • Use nvarchar when the sizes of the column data entries vary considerably and use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs. – Ilyes Nov 26 '18 at 21:24
  • You need to give an example, because it's really unclear what you're trying to do, and why. – pmbAustin Nov 26 '18 at 21:31
  • This is for script version logging , storing it in a table as a version record – goofyui Nov 26 '18 at 21:32
  • 4
    If this isn't an [xy problem](http://xyproblem.info/) I don't know what is. – Sean Lange Nov 26 '18 at 21:32
  • You need to enable `DATA ACCESS` on your server, then insert the results to your table – Ilyes Nov 26 '18 at 21:32
  • Possible duplicate of [Insert results of a stored procedure into a temporary table](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) – Ilyes Nov 26 '18 at 21:37
  • Why not use version control for your objects? You get the best of both worlds. – Sean Lange Nov 26 '18 at 21:37
  • Curious, why does source control not work for this? – squillman Nov 26 '18 at 21:37
  • This is not the Insert result of a stored procedure. This is insert of an actual Stored Procedure. Entire Stored Procedure (or) User Defined Function has to be inserted into a column – goofyui Nov 26 '18 at 21:38
  • So, you're reason for not wanting to use Source Control is..? – Thom A Nov 26 '18 at 21:49

2 Answers2

4

You could take a look at sys.sql_modules, which contains definitions (code) for database objects.

INSERT INTO [dbo].[some_table] ([schema_name], [object_name], [definition])
SELECT 
    OBJECT_SCHEMA_NAME([object_id]) [schema_name],
    OBJECT_NAME([object_id]) [object_name],
    [definition]
FROM sys.sql_modules
WHERE   OBJECT_SCHEMA_NAME([object_id]) = 'dbo'
    AND OBJECT_NAME([object_id]) = 'some_object'

Update: As others have commented, if the purpose is to maintain version history it may be more effective to use some other source code control solution. Also, if you want to track any time code in database objects change you could look into implementing a DDL trigger. Just searching "ddl trigger to track schema changes" produced some promising results.

Also, I just stumbled across OBJECT_DEFINITION(), which may be helpful:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.spt_values'))
Eilert Hjelmeseth
  • 618
  • 2
  • 5
  • 12
  • @Eilert , thank you.. this is helpful. But i just noticed the column : [definition] is trimmed some of the scripts. I am not able to get the complete script. – goofyui Nov 28 '18 at 15:19
  • It will be very helpful.. to get the whole script. Not as trimming partial script .. Because , we trying to load the whole complete script in a column – goofyui Nov 28 '18 at 15:22
  • I'm not sure how you're attempting to retrieve script, but if you're trying to copy/paste from query results in SQL Server Management Studio I'm guessing that is where it is getting truncated. You'll need to find a way to write the data to a file. You could perhaps use the import/export wizard or toss together a powershell script or something along those lines – Eilert Hjelmeseth Nov 28 '18 at 15:39
0

script is text. simply use the regular 'insert into' that sql server has.

when that script is inside a file, you need to read the contents of that file first. the method of doing that depends on the type of language you use - c, c#, java or python (whichever).

if you want to retrieve it, use the normal 'select' command.

however, I do not believe it's a good way of storing functions. being inside a file-system works (usually).

ilan Goldenberg
  • 19
  • 9