Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- || T-SQL script to generate pretty SELECTs to start researching an undocumented SQL Server database
- */
- declare @crlf varchar(2);
- set @crlf = CHAR(13); /* crlf is really CHAR(10)+CHAR(13) but doesn't print right in messages window */
- declare @query_catalog nvarchar(50),
- @query_schema nvarchar(50),
- @query_table nvarchar(50),
- @query_column nvarchar(50),
- @query nvarchar(max);
- declare table_cursor cursor for
- select TABLE_CATALOG,
- TABLE_SCHEMA,
- TABLE_NAME
- from INFORMATION_SCHEMA.TABLES
- order by TABLE_CATALOG asc,
- TABLE_SCHEMA asc,
- TABLE_NAME asc;
- open table_cursor;
- /* Get initial table */
- fetch next from table_cursor
- into @query_catalog,
- @query_schema,
- @query_table;
- /* Start table cursor loop */
- while @@FETCH_STATUS = 0 begin
- set @query = '/* ' + @query_catalog + '.' + @query_schema + '.' + @query_table +
- @crlf + /* yes, this is how i like my comments. */
- '*/' + @crlf + 'select ';
- declare column_cursor cursor for
- select COLUMN_NAME
- from INFORMATION_SCHEMA.COLUMNS
- where TABLE_CATALOG = @query_catalog
- and TABLE_SCHEMA = @query_schema
- and TABLE_NAME = @query_table
- order by ORDINAL_POSITION asc
- open column_cursor;
- /* Get the initial column */
- fetch next from column_cursor
- into @query_column;
- set @query = @query + '[' + @query_column + ']'
- /* Attempt to get another column from the table */
- fetch next from column_cursor
- into @query_column;
- /* If there's any more columns, print them all pretty */
- while @@FETCH_STATUS = 0 begin
- set @query = @query + ',' + @crlf + ' [' + @query_column + ']';
- fetch next from column_cursor
- into @query_column;
- end;
- close column_cursor;
- deallocate column_cursor;
- set @query = @query + @crlf + ' from [' + @query_catalog + '].[' + @query_schema + '].[' + @query_table + ']'+ @crlf;
- print @query;
- /* Attempt to get the next table */
- fetch next from table_cursor
- into @query_catalog,
- @query_schema,
- @query_table;
- end; /* table_cursor loop */
- close table_cursor;
- deallocate table_cursor;
Add Comment
Please, Sign In to add comment