Created
March 27, 2013 00:34
-
-
Save mcauser/5250601 to your computer and use it in GitHub Desktop.
SQL Server script for exporting a table to json
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Author: Thiago R. Santos | |
-- Create date: Aug 3rd 2008 | |
-- Description: Returns the contents of a given table | |
-- in JavaScript Object Notation. | |
-- Params: | |
-- @table_name: the table to execute the query | |
-- @registries_per_request: equivalent to "select top N * from table" | |
-- | |
-- replacing N by the actual number | |
-- Influenced by Thomas Frank's post MySQL to JSON @ January 23, 2007 | |
-- Post Url: http://www.thomasfrank.se/mysql_to_json.html | |
-- | |
-- Author: Mike Causer | |
-- Date: 2013-03-27 | |
-- Modifications: added support for schemas | |
-- http://stackoverflow.com/questions/1562128/how-can-i-get-a-json-object-from-a-sql-server-table | |
-- eg. | |
-- exec dbo.GetJSON 'dbo', 'members' | |
-- exec dbo.GetJSON 'dbo', 'members', 2 | |
create procedure [dbo].[GetJSON] ( | |
@schema_name varchar(50), | |
@table_name varchar(50), | |
@registries_per_request smallint = null | |
) | |
as | |
begin | |
if ( ( select count(*) from information_schema.tables where table_schema = @schema_name and table_name = @table_name ) > 0 ) | |
begin | |
declare @json varchar(max), | |
@line varchar(max), | |
@columns varchar(max), | |
@sql nvarchar(max), | |
@columnNavigator varchar(50), | |
@counter tinyint, | |
@size varchar(10) | |
if (@registries_per_request is null) | |
begin | |
set @size = '' | |
end | |
else | |
begin | |
set @size = 'top ' + convert(varchar, @registries_per_request) | |
end | |
set @columns = '{' | |
declare schemaCursor cursor for | |
select column_name | |
from information_schema.columns | |
where table_schema = @schema_name | |
and table_name = @table_name | |
open schemaCursor | |
fetch next from schemaCursor into @columnNavigator | |
select @counter = count(*) | |
from information_schema.columns | |
where table_schema = @schema_name | |
and table_name = @table_name | |
while @@fetch_status = 0 | |
begin | |
set @columns = @columns + '''''' + @columnNavigator + ''''':'''''' + convert(varchar, ' + @columnNavigator + ') + ''''''' | |
set @counter = @counter - 1 | |
if ( 0 != @counter ) | |
begin | |
set @columns = @columns + ',' | |
end | |
fetch next from schemaCursor into @columnNavigator | |
end | |
set @columns = @columns + '}' | |
close schemaCursor | |
deallocate schemaCursor | |
set @json = '[' | |
set @sql = 'select ' + @size + '''' + @columns + ''' as json into tmpJsonTable from [' + @schema_name + '].[' + @table_name + ']' | |
exec sp_sqlexec @sql | |
select @counter = count(*) from tmpJsonTable | |
declare tmpCur cursor for | |
select * from tmpJsonTable | |
open tmpCur | |
fetch next from tmpCur into @line | |
while @@fetch_status = 0 | |
begin | |
set @counter = @counter - 1 | |
set @json = @json + @line | |
if ( 0 != @counter ) | |
begin | |
set @json = @json + ',' | |
end | |
fetch next from tmpCur into @line | |
end | |
set @json = @json + ']' | |
close tmpCur | |
deallocate tmpCur | |
drop table tmpJsonTable | |
select @json as json | |
end | |
end |
Any updates ???
I really looking foreword for implementing this procedure to our DB
Invalid object name 'information_schema.tables'.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The only problem I find with this is when there are null values in the table, how could this be modified to handle null values as blank fields?