Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here's a proc that will export SOME types of data. </p> <pre><code>if exists ( select * from sysobjects where name = 'ExportData_P' ) drop proc ExportData_P go CREATE PROC dbo.ExportData_P ( @tableName varchar(500), @where varchar(5000) = '(1=1)' ) AS BEGIN SET NOCOUNT ON DECLARE @sql varchar(8000) DECLARE @fieldList varchar(8000) DECLARE @valueList varchar(8000) SELECT @fieldList = '', @valueList = '' DECLARE @cols TABLE ( column_name nvarchar(250), data_type varchar(250) ) DECLARE @c nvarchar(250), @data_type varchar(250) INSERT INTO @cols select column_name, data_type from information_Schema.columns where table_name = @tableName WHILE EXISTS ( SELECT TOP 1 * FROM @cols ) BEGIN SELECT TOP 1 @c = column_name, @data_type = data_type FROM @cols SELECT @fieldList = @fieldList + @c + ', ', @valueList = @valueList + CHAR(13) + 'case when ' + @c + ' is null then ''NULL'' else '''''''' + ' + case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then ' REPLACE ( REPLACE ( REPLACE ( ' else '' end + 'IsNull ( convert(varchar' + ( -- change this section to pass the length of varchar to convert case when @data_type in ( 'uniqueidentifier' ) then '(50)' when @data_type in ( 'text', 'ntext' ) then '(8000)' else '' end ) + ', ' + @c + '), '''' )' + -- end is null case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then ', CHAR(39), CHAR(39)+CHAR(39) ), CHAR(13), '''' + CHAR(13) + ''''), CHAR(9), '''' + CHAR(9) + '''') ' else '' end + ' + '''''''' end + '', '' + ' DELETE FROM @cols WHERE column_name = @c END SELECT @fieldList = LEFT ( @fieldList, LEN(@fieldList)-1 ), @valueList = LEFT ( @valueList, LEN(@valueList)-1 ) SELECT @sql = 'select ''insert into ' + @tableName + ' (' + @fieldList + ') ' + ' VALUES ( ''+ ' + left ( @valueList, len(@valueList)-5) + ''') '' from ' + @tableName + ' WHERE ' + @where -- into [#mcoe_temp_export' + @tableName + '] print @sql EXEC ( @sql ) --EXEC ( 'select * from [#mcoe_temp_export' + @tableName + ']' ) SET NOCOUNT OFF END go </code></pre> <p>Use like:</p> <pre><code>exec ExportData_P 'tablename' </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POQuick way to backup SQL SP and Functions?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJamie
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COhow to get a list of table names? Then I use your SP to loop through...
      singulars
      1. PO
      1. USDavid.Chu.ca
    2. COselect top 10 * from sysobjects where xtype = 'U'
      singulars
      1. PO
      1. USJamie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload