Usage of this script:
If the exact table name is known then specify the table name to the stored procedure else include the wild cards
EXEC FindTable 'Employee'
EXEC FindTable '%Employee%'
Sandesh Segu,
2009-08-04 (first published: 2009-07-18)
Usage of this script:
If the exact table name is known then specify the table name to the stored procedure else include the wild cards
EXEC FindTable 'Employee'
EXEC FindTable '%Employee%'
Create Proc FindTable @TableName nVarchar(50) As /* Purpose : Search for a Table in all databases Author : Sandesh Segu Date : 17th July 2009 Version : 1.0 */ Create Table #temp (DatabaseName varchar(50),SchemaName varchar(50),TableName varchar(50)) Declare @SQL Varchar(500) Set @SQL='Use [?] ; if exists(Select name from sys.tables where name like '''+@TableName+''') insert into #temp Select ''?'' AS DatabaseName ,SS.Name AS SchemaName ,ST.Name AS TableName from sys.tables as ST , sys.schemas SS where ST.Schema_ID=SS.Schema_ID and ST.name like '''+@TableName+'''' EXEC sp_msforeachdb @SQL Select * from #temp Drop table #temp GO
The Data Trasformation Services are a powerful tool, and sometime its features are so useful that you’d like to invoke a DTS package not only from SQL Server but from an external program.
To do this you have several choices: you can use the DTSRun.exe tool or you can do it leveraging the SQL-DMO features.
Unfortunately if you’re developing a web application (ASP, ASP.Net or whatever you use) none of them seems to be the right choice: too much problems, too much effort and a very modest results. In addition none of these solutions can be called asynchronously: if you just need to implement a “fire-and-forget” technique, you just cannot do that!
The next evoution of T-SQL, which will be released in SQL Server 2005, contains a number of enhancements designed to allow you to write more powerful queries while keeping the code structured in a way that makes development and understanding it easier. Coming ever so closer to the SQL-99 specification with Common Table Expressions, new author Srinivas Sampath brings us an introduction to this new way of writing complex queries.
2007-09-22 (first published: 2005-03-02)
50,559 reads
One of the new tasks in SQL Server 2005 is the For Loop Container. In this article we will demonstrate a few simple examples of how this works. Firstly it is worth mentioning that the For Loop Container follows the same logic as most other loop mechanism you may have come across, in that it will continue to iterate whilst the loop test (EvalExpression) is true. There is a known issue with the EvalExpression description in the task UI being wrong at present. (SQL Server 2005 Beta 2).
One of the most interesting features that I'll explore is that you can now create WebServices in the database tier directly, without resorting to "add ons" or even the use of IIS at all. One of the many extensions to Transact SQL is the new CREATE ENDPOINT statement, which allows the developer to create an endpoint directly from SQL Server 2005, which hooks right into the Kernel Mode HTTP.SYS driver, exposing functions and stored procedures written either in T-SQL or native CLR methods/classes.
This article discusses the XML support built into SQL Server 2005. It shows how this support integrates with the client side programming support in the .NET Framework V2.0 and in native code such as OLEDB and SQLXML.