How to insert a sql script in a table column?
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
sql
sql-server tsql asked Nov 26 '18 at 21:17
goofyuigoofyui
1,181133565
|
show 7 more comments
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
sql
sql-server tsql asked Nov 26 '18 at 21:17
goofyuigoofyui
1,181133565
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?
– Larnu
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.
– Sami
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
4
If this isn't an xy problem I don't know what is.
– Sean Lange
Nov 26 '18 at 21:32
|
show 7 more comments
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
sql
sql-server tsql asked Nov 26 '18 at 21:17
goofyuigoofyui
1,181133565
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
sql
sql-server tsql sql
sql-server tsql asked Nov 26 '18 at 21:17
goofyuigoofyui
1,181133565
asked Nov 26 '18 at 21:17
goofyuigoofyui
1,181133565
edited Nov 26 '18 at 21:39
goofyui
asked Nov 26 '18 at 21:17
goofyuigoofyui
1,181133565
asked Nov 26 '18 at 21:17
goofyuigoofyui
1,181133565
asked Nov 26 '18 at 21:17
goofyuigoofyui
1,181133565
1,181133565
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?
– Larnu
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.
– Sami
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
4
If this isn't an xy problem I don't know what is.
– Sean Lange
Nov 26 '18 at 21:32
|
show 7 more comments
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?
– Larnu
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.
– Sami
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
4
If this isn't an xy problem I don't know what is.
– Sean Lange
Nov 26 '18 at 21:32
7
7
ntext??? No. nvarchar(max).– Gordon Linoff
Nov 26 '18 at 21:17
ntext??? No. nvarchar(max).– Gordon Linoff
Nov 26 '18 at 21:17
1
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?
– Larnu
Nov 26 '18 at 21:20
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?
– Larnu
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.
– Sami
Nov 26 '18 at 21:24
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.
– Sami
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
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
4
4
If this isn't an xy problem I don't know what is.
– Sean Lange
Nov 26 '18 at 21:32
If this isn't an xy problem I don't know what is.
– Sean Lange
Nov 26 '18 at 21:32
|
show 7 more comments
2 Answers
2
active
oldest
votes
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'))
answered Nov 26 '18 at 21:47
Eilert HjelmesethEilert Hjelmeseth
35819
Well done! Much cleaner than my first thought
– John Cappelletti
Nov 26 '18 at 21:48
@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
add a comment |
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).
answered Nov 26 '18 at 21:36
iggyiggy
116
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53489207%2fhow-to-insert-a-sql-script-in-a-table-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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'))
answered Nov 26 '18 at 21:47
Eilert HjelmesethEilert Hjelmeseth
35819
Well done! Much cleaner than my first thought
– John Cappelletti
Nov 26 '18 at 21:48
@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
add a comment |
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'))
answered Nov 26 '18 at 21:47
Eilert HjelmesethEilert Hjelmeseth
35819
Well done! Much cleaner than my first thought
– John Cappelletti
Nov 26 '18 at 21:48
@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
add a comment |
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'))
answered Nov 26 '18 at 21:47
Eilert HjelmesethEilert Hjelmeseth
35819
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'))
answered Nov 26 '18 at 21:47
Eilert HjelmesethEilert Hjelmeseth
35819
edited Nov 26 '18 at 21:55
answered Nov 26 '18 at 21:47
Eilert HjelmesethEilert Hjelmeseth
35819
answered Nov 26 '18 at 21:47
Eilert HjelmesethEilert Hjelmeseth
35819
answered Nov 26 '18 at 21:47
Eilert HjelmesethEilert Hjelmeseth
35819
35819
Well done! Much cleaner than my first thought
– John Cappelletti
Nov 26 '18 at 21:48
@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
add a comment |
Well done! Much cleaner than my first thought
– John Cappelletti
Nov 26 '18 at 21:48
@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
Well done! Much cleaner than my first thought
– John Cappelletti
Nov 26 '18 at 21:48
Well done! Much cleaner than my first thought
– John Cappelletti
Nov 26 '18 at 21:48
@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
@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
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
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
add a comment |
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).
answered Nov 26 '18 at 21:36
iggyiggy
116
add a comment |
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).
answered Nov 26 '18 at 21:36
iggyiggy
116
add a comment |
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).
answered Nov 26 '18 at 21:36
iggyiggy
116
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).
answered Nov 26 '18 at 21:36
iggyiggy
116
answered Nov 26 '18 at 21:36
iggyiggy
116
answered Nov 26 '18 at 21:36
iggyiggy
116
answered Nov 26 '18 at 21:36
iggyiggy
116
116
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53489207%2fhow-to-insert-a-sql-script-in-a-table-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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?
– Larnu
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.
– Sami
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
4
If this isn't an xy problem I don't know what is.
– Sean Lange
Nov 26 '18 at 21:32