October 30, 2018 at 12:44 pm
The purpose of the code is to;
Model an additional script to setthe Database Recovery Mode to ‘FULL’. Your script shall follow this template
DECLARE @cmd VARCHAR(MAX)
DECLARE cSimpleDBs CURSOR FOR
SELECT 'ALTER DATABASE [' + sd.name + '] SET RECOVERY FULL'
FROM sys.databases sd LEFTJOIN sys.database_mirroring dbm
ON sd.database_id = dbm.database_id
WHERE sd.recovery_model_desc <> 'FULL' AND sd.name NOT IN ('master', 'model', 'tempdb', 'msdb')
AND sd.source_database_id IS NULL and sd.state_desc= 'ONLINE' AND sd.is_published<> 1
AND dbm.mirroring_guid IS NULL
--iterate throughcursor to set databases into Simple Mode that are not already in Simple Mode
OPEN cSimpleDBs
FETCH NEXT FROM cSimpleDBs INTO @cmd
while @@fetch_status = 0
BEGIN
Print @cmd
exec (@cmd)
FETCH NEXT FROM cSimpleDBs INTO @cmd
END
CLOSE cSimpleDBs
DEALLOCATE cSimpleDBs
Ordinarily I would go to the GUI and set the Recovery Model to "Full". Management has decided that we will do everything from a script that was written for me. On the 3rd line down there is a piece of code SELECT 'ALTER DATABASE [' + sd.name + '] SET RECOVERY FULL'
What the heck does the sd stand for in sd.name
October 30, 2018 at 12:49 pm
What the heck does the sd stand for in sd.name
sd is aliasing sys.databases
No offence, but if you're a DBA, you should know about table aliases 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 30, 2018 at 1:02 pm
I am getting there. Thank you. SSC Guru
October 30, 2018 at 2:24 pm
FROM sys.databases sd LEFTJOIN sys.database_mirroring dbm
As noted, a name, "sd", that follows immediately after a table name, "sys.databases", becomes an alias for that table. In fact, it becomes the only name for that table name reference in the query. That is, once you assign an alias you cannot use the original name.
Aliases are useful in most any query, to shorten the table names, but are critical in queries such as self-joins.
Finally, be consistent with alias naming. Use whatever pattern you prefer, but be consistent.
For "sys.databases", the alias "sd" includes the schema name. For "sys.database_mirroring", "dbm" does not. And db is used instead of d for database. Argghh.
Aliases should be:
sd and sdm OR
d and dm OR
db and dbm (my preference, since "db" is such a good alias for "database", and I don't include schema names in my aliases)
but not a mix of them.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 30, 2018 at 2:40 pm
You know I thought at first it was an alias and it turns out as you fine people pointed out to my dumb asrse that it was. I also found information about a hidden database name resource database. I could see in the code where is called for all the system databases I was familiar master, model, temp and msdb.and I had to wonder why it would be looking to set other database recovery models. I have to wait until tomorrow to run the script but I plan on putting the resource database in there. Thanks again for your assistance. RT
October 30, 2018 at 3:03 pm
Actually, you don't need to add the resource database as it isn't even listed in sys.databases.
October 30, 2018 at 3:11 pm
ScottPletcher - Tuesday, October 30, 2018 2:24 PMFROM sys.databases sd LEFTJOIN sys.database_mirroring dbm
As noted, a name, "sd", that follows immediately after a table name, "sys.databases", becomes an alias for that table. In fact, it becomes the only name for that table name reference in the query. That is, once you assign an alias you cannot use the original name.
Aliases are useful in most any query, to shorten the table names, but are critical in queries such as self-joins.
Finally, be consistent with alias naming. Use whatever pattern you prefer, but be consistent.
For "sys.databases", the alias "sd" includes the schema name. For "sys.database_mirroring", "dbm" does not. And db is used instead of d for database. Argghh.
Aliases should be:
sd and sdm OR
d and dm OR
db and dbm (my preference, since "db" is such a good alias for "database", and I don't include schema names in my aliases)
but not a mix of them.
As in all things SQL, it depends. We have a database where we have a schema set up for working tables, so we're often copying data between two tables with different schemata, but the same table name. I don't include the schema in the alias for the table in the main schema, but I do include it in the alias for the "working" schema.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 30, 2018 at 4:58 pm
drew.allen - Tuesday, October 30, 2018 3:11 PMAs in all things SQL, it depends. We have a database where we have a schema set up for working tables, so we're often copying data between two tables with different schemata, but the same table name. I don't include the schema in the alias for the table in the main schema, but I do include it in the alias for the "working" schema.Drew
I'm not overly keen on going down the route of deriving aliases by some rigorous abbreviation of the table names, that all too often ends up with aliases like F1, F2, F3 because the abbreviations clash. I prefer short but meaningful names every time, even at the expense of a little bit of consistency - the scope of aliases is fairly small after all.
October 31, 2018 at 7:37 am
andycadley - Tuesday, October 30, 2018 4:58 PMdrew.allen - Tuesday, October 30, 2018 3:11 PMAs in all things SQL, it depends. We have a database where we have a schema set up for working tables, so we're often copying data between two tables with different schemata, but the same table name. I don't include the schema in the alias for the table in the main schema, but I do include it in the alias for the "working" schema.Drew
I'm not overly keen on going down the route of deriving aliases by some rigorous abbreviation of the table names, that all too often ends up with aliases like F1, F2, F3 because the abbreviations clash. I prefer short but meaningful names every time, even at the expense of a little bit of consistency - the scope of aliases is fairly small after all.
Small, eh? Try working with hundreds of production tables where the occasional self-join is necessary. I've got table aliases like EDCCA that are necessary because not adhering to a fairly strict set of standard aliases can seriously confuse anyone making changes, because of the assumptions you can't help but start to make. It's actually easier to adhere to the standard in our case than to try and branch out. The table names are often VERY similar across perhaps 20 to 30 tables that deal with a particular segment of the application. So a small scope for aliases is utterly impractical for us. We typically get out to 5 letters fairly often just with our enterprise data tables.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 31, 2018 at 7:49 am
sgmunson - Wednesday, October 31, 2018 7:37 AMandycadley - Tuesday, October 30, 2018 4:58 PMI'm not overly keen on going down the route of deriving aliases by some rigorous abbreviation of the table names, that all too often ends up with aliases like F1, F2, F3 because the abbreviations clash. I prefer short but meaningful names every time, even at the expense of a little bit of consistency - the scope of aliases is fairly small after all.
Small, eh? Try working with hundreds of production tables where the occasional self-join is necessary. I've got table aliases like EDCCA that are necessary because not adhering to a fairly strict set of standard aliases can seriously confuse anyone making changes, because of the assumptions you can't help but start to make. It's actually easier to adhere to the standard in our case than to try and branch out. The table names are often VERY similar across perhaps 20 to 30 tables that deal with a particular segment of the application. So a small scope for aliases is utterly impractical for us. We typically get out to 5 letters fairly often just with our enterprise data tables.
The aliases are in scope only for the current statement batch – not throughout entire procs or scripts. Perhaps that is what was meant by small?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 31, 2018 at 8:12 am
Phil Parkin - Wednesday, October 31, 2018 7:49 AMThe aliases are in scope only for the current statement batch – not throughout entire procs or scripts. Perhaps that is what was meant by small?
Perhaps, but imagine a long stored procedure (of which we have perhaps 1,000 or more), and then having different aliases throughout for the same object. Really bad coding at such point, so no, I can't quite go along with that. Long stored procedures are REALLY COMMON in my current assignment.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 31, 2018 at 8:36 am
sgmunson - Wednesday, October 31, 2018 8:12 AMPhil Parkin - Wednesday, October 31, 2018 7:49 AMThe aliases are in scope only for the current statement batch – not throughout entire procs or scripts. Perhaps that is what was meant by small?Perhaps, but imagine a long stored procedure (of which we have perhaps 1,000 or more), and then having different aliases throughout for the same object. Really bad coding at such point, so no, I can't quite go along with that. Long stored procedures are REALLY COMMON in my current assignment.
You should use the same alias for the same table in multiple queries in a stored procedure. For example using sys.tables I use tab for the alias. If I am using sys.tables in multiple separate queries in a stored procedure I will use that alias each time. I even use the same the same alias with each CTE where the table is used in a single query as the alias is only relevant in the CTE not across CTEs.
October 31, 2018 at 8:48 am
Lynn Pettis - Wednesday, October 31, 2018 8:36 AMsgmunson - Wednesday, October 31, 2018 8:12 AMPhil Parkin - Wednesday, October 31, 2018 7:49 AMThe aliases are in scope only for the current statement batch – not throughout entire procs or scripts. Perhaps that is what was meant by small?Perhaps, but imagine a long stored procedure (of which we have perhaps 1,000 or more), and then having different aliases throughout for the same object. Really bad coding at such point, so no, I can't quite go along with that. Long stored procedures are REALLY COMMON in my current assignment.
You should use the same alias for the same table in multiple queries in a stored procedure. For example using sys.tables I use tab for the alias. If I am using sys.tables in multiple separate queries in a stored procedure I will use that alias each time. I even use the same the same alias with each CTE where the table is used in a single query as the alias is only relevant in the CTE not across CTEs.
Not sure you interpreted my statement correctly. I would certainly NOT want a different alias for the same object in different parts of the procedure or script. If I had a really small scope for aliases, I might well be forced into such behavior, which is why I can't support that concept. Scope for aliases, from my perspective, needs to be rather broad.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 31, 2018 at 8:56 am
sgmunson - Wednesday, October 31, 2018 8:48 AMLynn Pettis - Wednesday, October 31, 2018 8:36 AMsgmunson - Wednesday, October 31, 2018 8:12 AMPhil Parkin - Wednesday, October 31, 2018 7:49 AMThe aliases are in scope only for the current statement batch – not throughout entire procs or scripts. Perhaps that is what was meant by small?Perhaps, but imagine a long stored procedure (of which we have perhaps 1,000 or more), and then having different aliases throughout for the same object. Really bad coding at such point, so no, I can't quite go along with that. Long stored procedures are REALLY COMMON in my current assignment.
You should use the same alias for the same table in multiple queries in a stored procedure. For example using sys.tables I use tab for the alias. If I am using sys.tables in multiple separate queries in a stored procedure I will use that alias each time. I even use the same the same alias with each CTE where the table is used in a single query as the alias is only relevant in the CTE not across CTEs.
Not sure you interpreted my statement correctly. I would certainly NOT want a different alias for the same object in different parts of the procedure or script. If I had a really small scope for aliases, I might well be forced into such behavior, which is why I can't support that concept. Scope for aliases, from my perspective, needs to be rather broad.
Not sure how you can scope an alias beyond the query it is used. That just doesn't make sense. That an alias for a table can be used in a CTE and the same alias used for the same table in the outer query that uses the CTE makes sense to me as it is simply dynamically created single use (meaning the query it is defined in) view.
So, please define what you mean by broadly scoped.
I prefer to use table aliases as it makes reading the code easier for me.
October 31, 2018 at 2:10 pm
Yes, by "small" scope I meant it's only guaranteed to be in effect for a single query. It might be a good idea not to confuse people by changing aliases for the same object in multiple queries in a procedure (and I mostly agree there) but that's a convention, not a scope.
What I mean by not deriving them from the name of the table is that it often devolves into poor readability when you start having self-joins or joins to the same table multiple times. For example, if you have a "Person" table that is joined to twice, once for the manager details and the other the employee, I'd much rather see aliases like MGR and EMP than P1, P2 because it's a lot easier to understand. YMMV.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply