In our free SDU Tools for developers and DBAs, we’ve added a lot of scripting tools. The tool that I’m describing today is one of the most sophisticated tools in our scripting options. It’s ScriptTable.
It’s very flexible. For example, it can change the name of the table, or the schema that it’s in. It can force ANSI_NULLS and ANSI_PADDING on or off. It can change user-defined data types to their base types, change compression strategies, and more.
2019-01-30
In our free SDU Tools for developers and DBAs, we’ve added a lot of scripting tools. To script out SQL authenticated logins (as opposed to Windows logins), we have ScriptSQLLogins.
You can see how to use it in the main image above. It’s a function, that takes a list of the logins to script. You can pass the value ALL or a comma-delimited list.
Importantly, as well as the login and the hashed password, it also scripts other details:
2019-01-23
In our free SDU Tools for developers and DBAs, there’s another useful scripting tool. It scripts out the logins that are members of server roles. It’s called ScriptServerRoleMembers.
One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.
But if you just want to create a script, that works too.
By default, it lists permissions for all logins, but you can also provide a list of the logins that you’re interested in, as a comma-delimited list, to the @LoginsToScript parameter.
2019-01-16
In our free SDU Tools for developers and DBAs, there’s another useful scripting tool. It scripts out the server permissions that have been granted to logins. It’s called ScriptServerPermissions.
One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.
But if you just want to create a script, that works too.
By default, it lists permissions for all logins, but you can also provide a list of the logins that you’re interested in, as a comma-delimited list, to the @LoginsToScript parameter.
2019-01-09
In our free SDU Tools for developers and DBAs, there’s a tool that makes it easy to create a script for creating SQL Server database users. No surprise, it’s called ScriptDatabaseUsers.
One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.
But if you just want to create a script, that works too.
2019-01-02
I’m often wondering about how large different objects in my databases are. There are reports to provide that info, but I often want it in a programmatic form. One of our free SDU Tools for developers and DBAs does just that. It’s ListUserTableAndIndexSizes.
You can see how to execute it in the main image above. The procedure takes these parameters:
@DatabaseName sysname - This is the database to process @SchemasToList nvarchar(max) -> ‘ALL’ or comma-delimited list of schemas to list @TablesToList nvarchar(max) -> ‘ALL’ or comma-delimited list of tables to list @ExcludeEmptyIndexes bit -> 0 for list all, 1 for don’t list empty objects @ExcludeTableStructure bit -> 0 for list all, 1 for don’t list base table (clustered index or heap) @IsOutputOrderedBySize bit -> 0 for alphabetical, 1 for size descending
2018-12-26
SQL Server databases are often littered with indexes that aren’t needed. In many cases, people won’t even remember why they were created in the first place. So I’m often checking out indexes that might be candidates for removal. One of our free SDU Tools for developers and DBAs does just that. It’s ListUnusedIndexes.
You can see how to execute it in the main image above. The procedure takes just one parameter:
2018-12-19
Years ago, it was common to need to print checks (cheques) from computer systems. Fortunately checks have now pretty much disappeared in most countries. One of the challenges with printing a check, was that you needed to convert an amount of money into words. Even though checks are on the wane, it’s still important to do that on some contracts, etc. In our free SDU Tools for developers and DBAs, we added a function NumberAsText to do just that.
2018-12-12
I’m often reviewing existing databases and one of the first things I go looking for is the way they’ve used data types. In particular, I’m keen to know if they’ve used any deprecated data types (ie: ones that will/might be removed at some point). In our free SDU Tools for developers and DBAs, we added a procedure ListUseOfDeprecatedDataTypes to do just that, and to provide their details in a form that’s easy to consume programmatically if you need that.
2018-12-05
Ever since we’ve been shipping our free SDU Tools for developers and DBAs, we’ve been getting interesting requests from users for additions to the tools. Today’s tool is one of the stranger requests but for the odd situation where you need it, I’m sure we will have saved you a lot of work. That tool is NumberToRomanNumerals.
The person who requested it was outputting values that need to go into media trailers, pretty much as you see Roman numerals in the tail end of many movies.
2018-11-28
