Posts

Showing posts from April, 2022

Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr
Post a Comment
Read more

SQL Script: Size of all tables on all databases

-
Image
Sometimes I get emails from dbas saying our database is filling up and I want to know where the biggest problems are. I'm sure there are some DBA tools to help with that, but I find a really quick easy way is to execute sp_executesql for every table in every database. Here's a script you can run to  get all table sizes on all databases . It basically loops over all database (except those you don't care about, e.g. master, msdn, etc.) and gets the table sizes for each table. At the end, you can then aggregate things by database, or drill into a specific database for table-level details. set nocount, xact_abort on declare @ExclDatabases nvarchar( max ) = 'master|msdb|tempdb|ssisdb|model' , @Schemas nvarchar( max ) = null drop table if exists #AllTabs create table #AllTabs ( DbName nvarchar(128), SchemaName nvarchar(128), TableName nvarchar(128), ObjectId int, RowCt bigint, ReservedKB bigint, DataKB bigint, IndexSizeKB
Post a Comment
Read more

Create CSVs from SQL Server

-
 So you want to automate export some data from SQL to CSV files?  For one-off things, there are a couple approaches you can use SSMS for, however there are some major drawbacks of doing things there, most notably, it's all manual; you can't script any of this stuff out. Still, I'll discuss a few methods in SSMS before the cool stuff which is with BCP or Powershell SSMS These methods work fine for one-time exports that you don't need to automate and you don't need a high level of control of the output. As a Result Set One simple way is to set the output to either text (CTRL-T) or to file (CTRL-F). If you set it to text, copy/paste the results into a text file, or if you're using results to file, just name the file anything .csv  (instead of .rpt) . Make sure to clean up any header (like column names, and the big row of hyphens)  and footer (like the rowcount, if you didn't set nocount on). You can adjust some of the settings those methods use to generate the
Post a Comment
Read more