Geeks With Blogs
Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done SQL Server 2005 Tricks Miscellaneous SQL Server scripts
Last n Months Display-Value Pair for DropDown List greater than a minimumm date
Create PROCEDURE [dbo].[ts_Dates] @isTo bit AS /* ---------------------------... --Last Dates Display-Value Pair for DropDown List for date greater than ---------------------------... exec [dbo].[ts_Dates] 0 exec [dbo].[ts_Dates] 1 */ declare @minToDate datetime declare @minFromDate datetime select @minToDate = min(<date>) from <table>; select @minFromDate = DATEADD(MONTH,-1,min(<da... from <table>; --print @minToDate --print ......

Posted On Tuesday, January 31, 2012 6:43 AM

Generate insert SQL for a table
Thanks to AndraaxAged Yak Warrior http://www.sqlteam.com/foru... Generate insert SQL for a table - Insert this stored proc: create proc [dbo].[generate_inserts] @table varchar(50) --Generate inserts for table @table AS declare @cols varchar(1000) declare @col varchar(50) /* generate_inserts 'PlanLevel1ControlResponsib... */ set @cols='' declare colcur cursor for select column_name from information_schema.columns where table_name=@table open colcur fetch next from colcur ......

Posted On Thursday, January 12, 2012 9:17 AM

list schema, table, row count in a database
---------------------------... --all info from Tables ---------------------------... SELECT * FROM sys.Tables ---------------------------... --Schema.name from Tables ---------------------------... SELECT '['+SCHEMA_NAME(schema_id)+... AS SchemaTable FROM sys.tables ---------------------------... --Schema, name from Tables ---------------------------... ......

Posted On Sunday, January 8, 2012 7:40 PM

Display Foreign Key Relationships and Name of the Constraint for Each Table in Database
Generate create script for all Foreign Keys Original article: http://connectsql.blogspot.... SELECT 'ALTER TABLE '+OBJECT_NAME(F.PARENT_OBJE... ' ADD CONSTRAINT' + F.NAME + ' FOREIGN KEY'+'('+COL_NAME(FC.PARENT... ')'+'REFRENCES '+OBJECT_NAME (F.REFERENCED_OBJECT_ID)+'(' +COL_NAME(FC.REFERENCED_OBJ... FROM SYS.FOREIGN_KEYS AS F INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_IDGet ......

Posted On Thursday, December 15, 2011 7:04 AM

SQL Server Interview Questions
User-Defined Functions Scalar User-Defined Function A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. Table-Value User-Defined Function An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL ......

Posted On Saturday, February 26, 2011 2:43 PM

Select comma separated result from via comma separated parameter
Select comma separated result from via comma separated parameter PROCEDURE [dbo].[GetCommaSepStringsBy... (@CommaSepNumericIds varchar(max)) AS BEGIN /* exec GetCommaSepStringsByCommaSe... '1xx1, 1xx2, 1xx3' */ DECLARE @returnCommaSepIds varchar(max); with cte as ( select distinct Left(qc.myString, 1) + '-' + substring(qc.myString, 2, 9) + '-' + substring(qc.myString, 11, 7) as myString from q_CoaRequestCompound qc JOIN dbo.SplitStringToNumberTabl... AS ......

Posted On Monday, February 14, 2011 11:34 AM

Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc
Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc: Derived form "Method 6" from a great article: · How to pass a list of values or array to SQL Server stored procedure · http://vyaskn.tripod.com/pa... Create PROCEDURE [dbo].[GetMyTable_ListByCom... (@CommaSepReqIds varchar(500)) AS BEGIN select * from MyTable q JOIN dbo.SplitStringToNumberTabl... AS s ON q.MyTableId = s.ID End ALTER FUNCTION [dbo].[SplitStringToNumberT... ......

Posted On Friday, May 28, 2010 1:56 PM

SQL Server - left join data sums/counts/averages to date range to account for zero days or zero weeks
---------------------------... --SQL Server - left join data sums/counts/averages to date range to account for zero days or zero weeks --use recursive common table expression (CTE) and nested CTEs to accumulate "data dates" over the latest "n" weeks ---------------------------... ---------------------------... ......

Posted On Tuesday, September 29, 2009 10:32 AM

List All Primary Keys and Foreign Keys
List All Primary Keys and Foreign Keys Original article: http://www.sqlteam.com/foru... set nocount on create table #PK(constraint_schema sysname not null, constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#PK primary key clustered(constraint_schema, constraint_name)) create table #cols(constraint_schema sysname not null, constraint_name sysname not null, column_name sysname not null, ordinal_position int not null, constraint PK_#PKcol primary key ......

Posted On Thursday, October 23, 2008 3:49 AM

find columns and tables in a view
find columns and tables in a view SELECT vObj.name AS vName, vObj.id AS vID, vObj.xtype AS vType, dep.depid, dep.depnumber, tObj.name AS tName, col.colid, col.name AS cName FROM sysobjects vObj LEFT OUTER JOIN sysdepends dep ON vObj.id = dep.id LEFT OUTER JOIN sysobjects tObj ON dep.depid = tObj.id LEFT OUTER JOIN syscolumns col ON dep.depnumber = col.colid AND tObj.id = col.id WHERE vObj.xtype = 'V' And vObj.category = 0 and vObj.name = 'vw1_inv_item_det' ORDER BY vObj.name, tObj.name, col.name ......

Posted On Tuesday, April 29, 2008 4:54 AM

Add a column to a table unless it already exists
original article: http://www.cryer.co.uk/bria... To add a column to a SQL Server database table, checking first that the column does not already exist: if not exists (select * from syscolumns where id=object_id('<table_nam... and name='<column_name>') alter table <table_name> add <column_name> <column_definition> where: <table_name> is the name of the table, <column_name> is the name of the column and <column_definition> ......

Posted On Thursday, March 1, 2007 6:34 AM

Writing CLR Stored Procedures in C# - Introduction to C#

http://sqlteam.com/item.asp?ItemID=21927

 

Posted On Monday, July 17, 2006 5:13 PM

dynamic SQL string execution
CREATE PROCEDURE [dbo].[spr_ProcName] @variableText varchar(3900) = null AS declare @ssql nvarchar(4000) set @ssql = 'SELECT ' set @ssql = @ssql +' , duhColumn set @ssql = @ssql +' FROM duhTable set @ssql = @ssql +' where duhColumn in (''' + @PV_variableText + ''')' print @ssql exec sp_executesql @ssql END ......

Posted On Sunday, July 16, 2006 4:01 PM

Find any datasbase Column by partial Name in any User Table
SQL Server: SELECT sysobjects.name as "Table", syscolumns.name as "Column" from sysobjects , syscolumns where sysobjects.id = syscolumns.id and sysobjects.xtype = 'u' and syscolumns.name like '%YourFieldNameGoesHere%' order by sysobjects.name, syscolumns.name ---------------------------... --Get Table names and Row Counts ---------------------------... SELECT [TableAndCount] = so.name +' - '+CAST(MAX(si.rows)asvarcha... Rows' , [Name] = so.name FROM sysobjects so, sysindexes ......

Posted On Saturday, July 15, 2006 2:47 PM

How to get SQL Server 2005 Diagrams to work

ALTER AUTHORIZATION ON DATABASE:: 'EnterYourDatabaseNameHere' TO valid_login

EXEC sp_dbcmptlevel 'EnterYourDatabaseNameHere', '90';

Posted On Saturday, July 15, 2006 2:43 PM

Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net