SHARE
TWEET

Untitled

a guest Dec 9th, 2018 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. || T-SQL script to generate pretty SELECTs to start researching an undocumented SQL Server database
  3. */
  4.  
  5. declare @crlf varchar(2);
  6. set @crlf = CHAR(13); /* crlf is really CHAR(10)+CHAR(13) but doesn't print right in messages window */
  7.  
  8. declare @query_catalog nvarchar(50),
  9.         @query_schema nvarchar(50),
  10.         @query_table nvarchar(50),
  11.         @query_column nvarchar(50),
  12.         @query nvarchar(max);
  13.  
  14. declare table_cursor cursor for
  15. select TABLE_CATALOG,
  16.        TABLE_SCHEMA,
  17.        TABLE_NAME
  18.   from INFORMATION_SCHEMA.TABLES
  19.  order by TABLE_CATALOG asc,
  20.        TABLE_SCHEMA asc,
  21.        TABLE_NAME asc;
  22.  
  23. open table_cursor;
  24.  
  25. /* Get initial table */
  26. fetch next from table_cursor
  27.  into @query_catalog,
  28.       @query_schema,
  29.       @query_table;
  30.  
  31. /* Start table cursor loop */
  32. while @@FETCH_STATUS = 0 begin
  33.  
  34. set @query = '/* ' + @query_catalog + '.' + @query_schema + '.' + @query_table +
  35.              @crlf + /* yes, this is how i like my comments. */
  36.              '*/' + @crlf + 'select ';
  37.  
  38. declare column_cursor cursor for
  39. select COLUMN_NAME
  40.   from INFORMATION_SCHEMA.COLUMNS
  41.  where TABLE_CATALOG = @query_catalog
  42.    and TABLE_SCHEMA = @query_schema
  43.    and TABLE_NAME = @query_table
  44.  order by ORDINAL_POSITION asc
  45.  
  46. open column_cursor;
  47.  
  48. /* Get the initial column */
  49. fetch next from column_cursor
  50.  into @query_column;
  51. set @query = @query + '[' + @query_column + ']'
  52.  
  53. /* Attempt to get another column from the table */
  54. fetch next from column_cursor
  55.  into @query_column;
  56.  
  57. /* If there's any more columns, print them all pretty */
  58. while @@FETCH_STATUS = 0 begin
  59. set @query = @query + ',' + @crlf + '       [' + @query_column + ']';
  60. fetch next from column_cursor
  61.  into @query_column;
  62. end;
  63.  
  64. close column_cursor;
  65. deallocate column_cursor;
  66.  
  67. set @query = @query + @crlf + '  from [' + @query_catalog + '].[' + @query_schema + '].[' + @query_table + ']'+ @crlf;
  68. print @query;
  69.  
  70. /* Attempt to get the next table */
  71. fetch next from table_cursor
  72.  into @query_catalog,
  73.       @query_schema,
  74.       @query_table;
  75.  
  76. end; /* table_cursor loop */
  77.  
  78. close table_cursor;
  79. deallocate table_cursor;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top