http://www.sqlservercentral.com/Content tagged T-SQL, Administering posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)This is a stored procedure to add a columns to multiple tables, if column doesn't existhttp://www.sqlservercentral.com/scripts/T-SQL/63236/2008/07/01http://www.sqlservercentral.com/scripts/T-SQL/63236/Attaching and detaching databases is old hat these days right? Do you know how to reattach a database that has more than 16 files? Or do you know what happens if you try to reattach a database that had two log files but one is missing/deleted? And even if you know the answer to that - do you know how to fix it without restoring from backup? Maybe it's not ALL old hat just yet! http://www.sqlservercentral.com/articles/Administering/attachanddetachagain/656/2006/06/23http://www.sqlservercentral.com/articles/Administering/attachanddetachagain/656/In the final installment of his series, Michael Coles examines a few other functions in his SQL Server 2000 toolkit to help you with other functions, like reading a directory on your server. Read the series and send some feedback to make this THE premier toolset for SQL Server 2000 DBAs. http://www.sqlservercentral.com/articles/Administering/sql2000dbatoolkitpart4/2364/2006/05/04http://www.sqlservercentral.com/articles/Administering/sql2000dbatoolkitpart4/2364/Part 2 of Steve Jones' series on beginning SQL Server from the perspective of a system administrator or someone not used to working with SQL Server. If you've been designated the new administrator, take a look at this series for some help in coming up to speed on this product. http://www.sqlservercentral.com/articles/Administering/beginningsqlserverlimityourqueries/1458/2006/04/14http://www.sqlservercentral.com/articles/Administering/beginningsqlserverlimityourqueries/1458/How many of you use Execution Plans to tune your queries? Do you understand the impact of different indexes? Mr. Vijayakumar looks at his experiments with different types of indexes and their effects on the execution plan use. A great article for those of you that want to learn more about how you can tune your server for better performance. http://www.sqlservercentral.com/articles/Administering/executionplans/1345/2005/10/07http://www.sqlservercentral.com/articles/Administering/executionplans/1345/Do you know the difference between a login and a user? What's the best way to add them; Enterprise Manager, T-SQL, or SQL-DMO? In this beginner level article Andy demonstrates how to use all three methods to add logins and users and offers his view of which is the best technique. http://www.sqlservercentral.com/articles/Administering/loginsusersandrolesgettingstarted/514/2005/09/30http://www.sqlservercentral.com/articles/Administering/loginsusersandrolesgettingstarted/514/DTS is an incredible package for moving data in the SQL Server world. One feature that is missing, however, is the ability to send files using FTP to a remote server. This article looks at a technique for sending files via FTP. http://www.sqlservercentral.com/articles/Administering/pushftp/473/2005/07/01http://www.sqlservercentral.com/articles/Administering/pushftp/473/Performing routine database maintenance such as reindexing is important for keeping your databases running at peak performance. When you use INSERT, UPDATE, and DELETE statements to modify table data, indexes can become fragmented. Index fragmentation can happen when the logical sequence of pages is disrupted or when an index page contains less than its maximum amount of data, creating a gap in the data page or index. As indexes become fragmented, you get inefficient data reads when accessing tables and slower database performance. http://www.sqlservercentral.com/redirect/articles/1772/2005/03/10http://www.sqlservercentral.com/redirect/articles/1772/Do you use the maintenance plans or hate them? Wish they would do more? Curious about how they work under the hood? Cmon, you gotta read this one! Trust us, it's not another "how-to" article! Well, maybe just a little bit! http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/2005/03/04http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/Mindy explores the metadata stored in SQL 2000 to show you how to produce a simple and useful data dictionary! http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/2005/02/04http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/In this article, Brian Kelley continues his series on Query Analyzer for SQL Server 2000. Query Analyzer offers a highly configurable integrated development environment (IDE). Some of the areas Brian looks at are how to modify the fonts and colors, set connection settings, choose scripting options, and customizing file and result set options. Learn how to make the most of the IDE and make it work for you. http://www.sqlservercentral.com/articles/Administering/qa2k_3/740/2005/01/07http://www.sqlservercentral.com/articles/Administering/qa2k_3/740/Brian Kelley continues his series on getting the most out of SQL Server 2000's Query Analyzer. In this article he looks at the Object Browser and the Transact-SQL Debugger, new features in the 2000 Edition which can reduce development and troubleshooting time for DBAs and database developers. http://www.sqlservercentral.com/articles/Administering/qa2k_2/622/2004/12/31http://www.sqlservercentral.com/articles/Administering/qa2k_2/622/So you open up the tool a hundred times a day. In this article by Brian Kelley, he shows you how to get the most out of some of the known and unknown features. http://www.sqlservercentral.com/articles/Administering/qa2k_1/614/2004/12/23http://www.sqlservercentral.com/articles/Administering/qa2k_1/614/One of the biggest performance gains built into SQL Server is the stored procedure. In this article by Brian Kelley, he shows you how to fully utilize, debug and monitor the caching of such objects. http://www.sqlservercentral.com/articles/Administering/procedurecache/591/2004/11/19http://www.sqlservercentral.com/articles/Administering/procedurecache/591/Part 2 of Steve Jones' series on beginning SQL Server from the perspective of a system administrator or someone not used to working with SQL Server. If you've been designated the new administrator, take a look at this series for some help in coming up to speed on this product. http://www.sqlservercentral.com/articles/Administering/beginningsqlserverlimityourqueries/1458/2006/04/14http://www.sqlservercentral.com/articles/Administering/beginningsqlserverlimityourqueries/1458/In this follow up Andy looks into some of the options available when you're dealing with situations where you're joining a case sensitive column to a case insensitive column. We think after reading this you'll be ready to just say no! http://www.sqlservercentral.com/articles/Administering/casesensitivityinselectspart2/1412/2004/06/22http://www.sqlservercentral.com/articles/Administering/casesensitivityinselectspart2/1412/See what a reader had to say about Part 1, make sure you've read Part 2, then feel the pain as the author describes a lookup table that should have had unique values and doesn't. http://www.sqlservercentral.com/articles/Administering/casesensitivityinselectspart3/1388/2004/06/15http://www.sqlservercentral.com/articles/Administering/casesensitivityinselectspart3/1388/Sysforeignkeys is a valuable SQL Server resource. How many times have you had to "pick up the pieces" from a database developed by someone else and dropped on your desk? Ever get a database diagram with that? A data dictionary? Probably not too often. Jeffrey Yao has developed a system of finding those parent-child relationships automatically and displaying them so he can get up to speed quickly on these inherited databases. Read on to find out more. http://www.sqlservercentral.com/articles/Administering/makinggooduseofsysforeignkeystablepart1displaytabl/1364/2004/06/01http://www.sqlservercentral.com/articles/Administering/makinggooduseofsysforeignkeystablepart1displaytabl/1364/Creating a system stored procedure isn't hard. You're always cautioned from making changes to the system, depending on the system for a particular functionality, etc., and you should be cautious. However adding system stored procedures to your servers can be beneficial and make your administration much more convenient. Read on the see how easy this can be to do. http://www.sqlservercentral.com/articles/Administering/creatingasystemstoredprocedure/1358/2004/05/20http://www.sqlservercentral.com/articles/Administering/creatingasystemstoredprocedure/1358/How many of you use Execution Plans to tune your queries? Do you understand the impact of different indexes? Mr. Vijayakumar looks at his experiments with different types of indexes and their effects on the execution plan use. A great article for those of you that want to learn more about how you can tune your server for better performance. http://www.sqlservercentral.com/articles/Administering/executionplans/1345/2005/10/07http://www.sqlservercentral.com/articles/Administering/executionplans/1345/For the most part the DTC (Distributed Transaction Coordinator) just works. What happens when it doesn't? Chris had to resolve an issue with it recently and documented some of the steps he used. http://www.sqlservercentral.com/articles/Administering/debuggingmsdtcissues/1257/2004/01/19http://www.sqlservercentral.com/articles/Administering/debuggingmsdtcissues/1257/Are you using alerts to help you keep track of things? Are you using as many alerts as you should be? Jeremy has a great list of alerts that he considers so important they are on his 'best practice' list. Definitely worth reading. http://www.sqlservercentral.com/articles/Administering/alertalertbackupandrestorebaby/1253/2004/01/08http://www.sqlservercentral.com/articles/Administering/alertalertbackupandrestorebaby/1253/Haidong continues to point out ideas that might get you thinking about ways you can do more administration with less work. In this article he demonstrates a couple useful tasks you can do with xp_cmdshell. http://www.sqlservercentral.com/articles/Administering/usingxp_cmdshell/1250/2004/01/05http://www.sqlservercentral.com/articles/Administering/usingxp_cmdshell/1250/One of the most handy extended stored procedures in SQL Server is xp_cmdshell. This article will show you how to use it. http://www.sqlservercentral.com/articles/Administering/xpcmdshell/141/2003/11/14http://www.sqlservercentral.com/articles/Administering/xpcmdshell/141/Another product review! Chris put AdeptSQL (a schema comparison and sync product) to work and wrote up the results. This article also includes some feedback from the vendor that is worth reading. http://www.sqlservercentral.com/articles/Administering/reviewofadeptsql/1173/2003/11/03http://www.sqlservercentral.com/articles/Administering/reviewofadeptsql/1173/Aladdin is an "alter table" script generation tool for SQL Server 7/2000. Aladdin is currently in BETA. They are currently looking for for 25 people willing to use the beta product and report their experiences. Users who fully participate in the beta will get a free, single user license when the product is released. To participate in the Aladdin BETA program, please download the software and send an email to Ken Otto: ottosoft@hotmail.com - Subject: Aladdin Beta Test. http://www.sqlservercentral.com/redirect/articles/1139/2003/10/01http://www.sqlservercentral.com/redirect/articles/1139/Don't like the blackbox approach of maintenance plans? Like the maintenance plans but need a tweak to suit your needs? New author Robin Back has put together a monster script to allow you to build your own quickly and easily. http://www.sqlservercentral.com/articles/Administering/createmaintenancejobwithaclickwithoutusingawizard/1102/2003/08/20http://www.sqlservercentral.com/articles/Administering/createmaintenancejobwithaclickwithoutusingawizard/1102/Collations hard to sort out (I know, bad joke)? Mike did what a good DBA gets paid to do, figure out an easy way to fix something and remove the chance of a user mistake at the same time. Good explanation and a couple scripts show how to handle column level collations. http://www.sqlservercentral.com/articles/Administering/changingroguedatabasecollations/994/2003/05/26http://www.sqlservercentral.com/articles/Administering/changingroguedatabasecollations/994/This article by Simon Galbraith (from Red Gate software, maker of SQL Compare) discusses migrating changes from development to staging, QA, and on to production. If you've never seen the need for a schema compare tool (Steve Jones!), this is worth reading.http://www.sqlservercentral.com/redirect/articles/982/2003/05/06http://www.sqlservercentral.com/redirect/articles/982/New author! Mike Pearson discusses how auto close was at the root of a performance problem he was troubleshooting, how you can check your servers easily, and discusses why both auto close and auto shrink are bad ideas in a production environment. http://www.sqlservercentral.com/articles/Administering/autocloseandautoshrinkjustdont/984/2003/05/05http://www.sqlservercentral.com/articles/Administering/autocloseandautoshrinkjustdont/984/