1

When I right click on 'schemas' and then on 'create new schema' my SSMS only opens the query editor but on every tutorial I saw a pop up wizard opens instead. How can I access this popup wizard? I can't find the right setting. I'm currently following the course Developing SQL Databases on edx but I've also seen the popup wizard on youtube tutorials. I'm using the version 17.9.1 of SSMS

Many thanks in advance!

New contributor
Matthias is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • While I agree that the CREATE SCHEMA command is so simple so that a GUI element for it seems like overkill, I'm curious why I do get this dialog and you don't. Are you perhaps connected to Azure SQL Database? I'm using an on-prem db engine (2017), and I do see that dialog. – Tibor Karaszi 2 days ago
  • In fact the tutorial I'm using requires a connection to an Azure DB. As you can see from David Bownes comment, this is the reason why I can't use this popup window – Matthias yesterday
3

How can I access this popup wizard?

This popup is disabled for Azure SQL Database and Azure SQL Data Warehouse. It works for on-prem SQL Server.

Why? Probably because it didn't work as-is against Azure SQL DB/DW, and it wasn't deemed worth fixing. This is a really low-value feature, as Schemas don't really belong to Security and creating them in TSQL is so simple.

If you run Profiler you can see that SSMS runs the following batch:

declare @MasterPath nvarchar(512)
declare @LogPath nvarchar(512)
declare @ErrorLog nvarchar(512)
declare @ErrorLogPath nvarchar(512)
declare @Slash varchar = convert(varchar, serverproperty('PathSeparator'))      
select @MasterPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'master'
select @LogPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'mastlog'
select @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512))
select @ErrorLogPath=IIF(@ErrorLog IS NULL, N'', substring(@ErrorLog, 1, len(@ErrorLog) - charindex(@Slash, reverse(@ErrorLog))))


declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

Which wouldn't work on SQL Database or SQL Data Warehouse.

  • Perfect, that's it! Thanks a lot – Matthias yesterday
2

Rather than depend upon the GUI, use T-SQL. For a basic setup without additional security & configuration, (which is likely the case if you're following a tutorial), it's a simple query.

USE YourDatabaseName;
GO
CREATE SCHEMA YourSchemaName;
  • Thanks and I'm absolutely with you. I just try to following the tutorial and get every aspect of the software. – Matthias Jan 13 at 14:48
  • If you can use T-SQL, you don't need the GUI. SSMS doesn't expose anything that you can't do via T-SQL or PowerShell. Do not focus on "learning every aspect" when there are better alternatives than clicking in dialog boxes. – alroc Jan 13 at 15:47
  • 1
    As a bonus, many of the dialog boxes in SSMS have a "Script To" action in the top left(ish) corner. You can use this to make your changes in the GUI and then see how the engine actually implements those changes. It's all TSQL under the hood, the GUI only makes it friendly. – Jonathan Fite Jan 14 at 4:14

Your Answer

Matthias is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.