To view that information, there are 2 primary ways:select * from tempdb.INFORMATION_SCHEMA.C... where table_name like '#MyTempTable%'select * from tempdb.sys.columns where object_id = object_id('tempdb..#mytempt... ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/11ec

This is a handy script to cycle through every character in a column to determine what each ascii value is. This is especially useful when a string match isn't matching. Often times, there is a hidden space, etc. DECLARE @counter int = 1;--DECLARE @asciiString varchar(10) = 'AA%#& '; DECLARE @asciiString varchar(100) SELECT @asciiString = [ColumnName] FROM schema.TableName where ColumnName like '%Something%'WHILE @counter <= DATALENGTH(@asciiString) BEGIN SELECT CHAR(ASCII(SUBSTRING(@ascii... ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/11e5

There are probably open sessions on the database you are attempting to bring offline. SQL Server is trying to roll back any existing workloads in-flight for that database. Issue the sp_who2 command from a new connection (master db) and view what's active. If you see activity, let it complete--or if you don't want the sessions to complete for whatever reason, issue the kill command for the spid(s). In the future, use this command:ALTER DATABASE yourDBName SET OFFLINE WITH ROLLBACK IMMEDIATE;To bring ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/11cv

If you have a very large data base (VLDB) a select count(*) can take a long time to resolve. Try this statement as an alternative:USE YourDatabasename;SELECT s.name AS 'SchemaName' ,o.name AS 'TableName' ,SUM(p.row_count) AS 'RowCount'FROM sys.dm_db_partition_stats p JOIN sys.objects o ON o.object_id = p.object_id JOIN sys.schemas s ON o.schema_id = s.schema_idWHERE p.index_id < 2 AND o.type = 'U' AND s.name = 'Schema' AND o.name = 'TableName'GROUP BY s.name,o.nameORDER BY s.name,o.name;GO ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/i5L

This will locate specific text within an object on a given database. In this case, it will find all views, stored procs which have the text string "Department" somewhere in the body.use [databaseInstance]goSELECT DISTINCT o.name AS Object_Name, o.type_descFROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like '%Department%'; ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/i2v

I've been there! You are at a client site and you know you need certain data elements but aren't certain how many databases on a given server ...or ... which tables the data elements you are interested in might appear. Here's a single statement when executed from any SMS query connection that will get you there: EXEC sp_msforeachdb ' DECLARE @pattern nvarchar(100) SET @pattern = ''%elementName%'' IF EXISTS(SELECT 1 FROM [?].information_schema.columns WHERE column_name LIKE @pattern) BEGIN SELECT ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/cMS

The easiest way in MS SQL 2005 and up is to use the SQL Management Studio, go to Views, highlight the name of the view you are interested in, right click, select Script View as/Create to/Clipboard. open up notepad or your prefered editor and paste the contents of your clipboard. The SQL used to create the view should show up in your editor. If this doesn't work, or if you are working with older versions of SQL Server, look at the system objects as follows: use [YouDatabseInstanceName] go select * ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/blS

Before I create views, I generally work out what I want to retrieve in my SELECT statement ahead of time so I'll just have to cut and paste the query. The example below is done in T-SQL/Sybase format, however for Oracle and MySQL, just place a semi-colon ';' at the end of your statement and remove the 'GO' command. To drop (delete) an existing view: DROP VIEW vw_rpt_metroBestCustomers GO To create a view: CREATE VIEW vw_rpt_metroBestCustomers ( CustomerName, OfficeNum, City, StateOrProv, Country, ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/bTb

This one will order the contraints by table: Select SysObjects.[Name] As [Contraint Name] ,Tab.[Name] as [Table Name],Col.[Name] As [Column Name] From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab On Tab.[ID] = Sysobjects.[Parent_Obj] Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID] Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID] order by Tab.[Name] This one will order the contraints by column: Select ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/b68

The objects that help you find this info in SQL Server are in sys.tables and sys.columns SELECT tbl.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, col.name AS column_name FROM sys.tables AS tbl INNER JOIN sys.columns col ON tbl.OBJECT_ID = col.OBJECT_ID WHERE col.name LIKE '%YourColumnNameHere%' ORDER BY schema_name, table_name; This will bring back every table that has a reference to '%YourColumnNameHere%' ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/azu

There's more than one reason why you may receive this error, but the most common reason is that your order by statement column list doesn't correlate with your intended column-to-retrieve list when you happen to be using DISTINCT. This is usually easy to spot. A more obscure reason may be that you are using a function around one of the selected columns --but omitting to use the same function around the same selected column name in the order by statement. Here's an example: select distinct upper(columnA) ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/aNC

To create a index on a single column, do this:

CREATE INDEX  IDX_FERC ON FERC(FercName)

In some cases, you may have a need to combine columns to hasten known unique combinations. This is knonw as a composite key. An example:

CREATE INDEX IDX_OperatingCompanyFERC ON FERC(OpCompanyID,FercName)

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/ZMo

If you want to reformat data, use the function convert as shown:

select convert(varchar, cast(18 as integer)) as 'a number'

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/Yqm

First, for all of the MS SQL Server database purists out there that will complain vehemently against this method of extraction/formatting because of the special characters (SEE: http://www.webmonkey.com/re... that may (will likely) surface and need to be escaped, or that there's a better way to do this such as utilizing the XML Schema Collection/XML Explicit features available with SQL Server, etc. -- I recognize your concern, however, you'll have to admit, this is still (low-tech/mid-90's ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/YT2

This is a simple, but helpful example of changing a boolean or bit flag in a table to 'True' when the condition in the subSelect is met. In this context, the SQL statement finds all the cases where a sales agent's email exists in the Metro_SalesForce table and exists in the related table Metro_SalesForceMult. The Metro_SalesForceMult table contains the USA state assignments only for sales agents representing more than a single state. UPDATE Metro_SalesForce SET multipleStates = 'True' WHERE Metro_SalesForce.email ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/YT1

Yes! .....(Now, we're not talking about bulk inserts--which is a completely different task... we're just talking about a small number of rows that need to get inserted into a relatively static table).. Typically folks do something like this to quickly add rows into a reference table: INSERT INTO ReasonType (DisplayName,Description) VALUES ('Delay','Reason for a project delay') GO INSERT into ReasonType (DisplayName,Description) VALUES ('Cancellation','Reaon for a project cancellation') GO ...but ......

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/XSi