How to have all the column name of a table in one single string, seperated by comma, on one column..

This will allow you to have all the column name in one column, seperated by comma,

This way you can do the input file validation too and there are many other ways these could be used.
DECLARE @Names VARCHAR(8000)
DECLARE @TBLNAME VARCHAR(100)
SET @TBLNAME=’Employee’

SELECT @Names =COALESCE(@Names + ‘, ‘, ”)+ c.name from sys.tables t join sys.columns c on c.object_id = t.object_id
where t.name = ”+@TBLNAME+”
Select @Names as ColumnHeader

 

 

columnnameinstring

Advertisements

How to Check for table name or stored proc across the SQL server

CREATE PROCEDURE [dbo].[usp_FindTable_StoredProcNameInAllDatabases]
@SearchName VARCHAR(256)
AS
DECLARE @MYDBName VARCHAR(256)
DECLARE @SQL VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT QUOTENAME([name])
FROM sys.databases /*where left(name,3) =’AL_’*/ –This part is optional, if you want to filter or narrow down on which databases to search, rather then searching all databases in server
CREATE TABLE #TmpTable (
DBName VARCHAR(256),
SchemaName VARCHAR(256),
NameofStoredProcOrTable VARCHAR(256),
Type VARCHAR(10)
)
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @MYDBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘USE ‘ + @MYDBName + ‘;
INSERT INTO #TmpTable
SELECT ”’+ @MYDBName + ”’ AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS NameofStoredProcOrTable,
”Table” AS Type
FROM sys.tables
WHERE name LIKE ”%’ + @SearchName + ‘%”’
EXEC (@SQL)

SET @SQL = ‘USE ‘ + @MYDBName + ‘;
INSERT INTO #TmpTable
SELECT ”’+ @MYDBName + ”’ AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS NameofStoredProcOrTable,
”StrProc” AS Type
FROM sys.procedures
WHERE name LIKE ”%’ + @SearchName + ‘%”’
EXEC (@SQL)

FETCH NEXT
FROM @getDBName INTO @MYDBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable Order By Type
DROP TABLE #TmpTable

GO

Now Execute the stored Proc

EXEC usp_FindTable_StoredProcNameInAllDatabases @SearchName=’whatevermystoredprocnameis

 

 

 

This stored procedure will help you find the table or stored proc that you have across all the database in sql server

Check for house number in address string (if is numerical) SSIS

First Create a Data Flow Task.

Inside the Data Flow Task, Drag a Source as Flat File Source

Here we have a flat file with two columns id and Address

address

So in the Data Flow Task 1, I have Flat File Source where i have my address file hooked up.

Next, drag a derived column and on the derived column we will use the Token function that we have in SSIS.

parseaddress

token

Next drag a multi cast, and add enable data viewer to see what the code does,

And run it, It should show as below..

housenum

Determine AM or PM from your date time

–use datepart to determine AM or PM
——–
Declare @timestamp datetime
Set @timestamp =GETDATE();
Select
case when datepart(HOUR,@timestamp) > 12 then cast(datepart(HOUR,@timestamp)-12 as varchar) +’PM’
else
case when datepart(HOUR,@timestamp) = 12 then cast(datepart(HOUR,@timestamp) as varchar) +’PM’
else cast(datepart(HOUR,@timestamp) as varchar) +’AM’
end
end as hour

Get Count of all the tables in the database

/*Looping throug each tables in the database and getting the counts of all the tables in the database
Here we are first creating a temporary table called #CountsEachTable. In that table we will insert values from the @command1 that is executed via
the stored proc called sp_MSForEachTable..
Finally Selecting it and dropping the temp table..*/

CREATE TABLE #CountsEachTable
(
my_table_name varchar(255),
row_count int
)

EXEC sp_MSForEachTable @command1=’INSERT #CountsEachTable (my_table_name, row_count) SELECT ”?”, COUNT(*) FROM ?’
SELECT my_table_name, row_count FROM #CountsEachTable ORDER BY my_table_name, row_count DESC
DROP TABLE #CountsEachTable

 

Alternative way  Continue reading “Get Count of all the tables in the database”

Parse string with delimiters

This is to parse any string with similar delimiters,

Hope this helps some one….

 

Declare @MyString nvarchar(50);
Set @MyString =’AB_123458_2017-01-01_BDEAE’

select SUBSTRING(@MyString,0,CHARINDEX(‘_’,@MyString)) as FirstTwoValue,
SUBSTRING(Ltrim(REPLACE(@MyString,LEFT(@MyString,3),’ ‘)),0,CHARINDEX(‘_’,Ltrim(REPLACE(@MyString,LEFT(@MyString,3),’ ‘)))) as AfterFirstDash ,
REVERSE(REPLACE(SUBSTRING(REVERSE(@MyString),CHARINDEX(‘_’,REVERSE(@MyString)),12),’_’,”)) as afterseconddash,
RIGHT(@MyString,Charindex(‘_’,Right(@MyString,10))) as laststring,
@MyString as FullValue

 

output will loook like

stringparse

Cursor to loop thro each database and get the result you want for each db each tables.

–This is a cursor for looping thro each database in your server and getting  your sql statement executed..

 

DECLARE AllDatabases CURSOR FOR

SELECT cast(name as nvarchar(256)) as name FROM master.dbo.sysdatabases WHERE dbid > 4

 

OPEN AllDatabases

 

DECLARE @DBNameVar NVARCHAR(256),@Statement NVARCHAR(500)

Declare @dbname nvarchar (500)

FETCH NEXT FROM AllDatabases INTO @DBNameVar

WHILE (@@FETCH_STATUS = 0)

BEGIN

set @dbname =N’CHECKING DATABASE ‘ + @DBNameVar;

PRINT N’CHECKING DATABASE ‘ + @DBNameVar

SET @Statement = N’USE [‘ + @DBNameVar + ‘]’+CHAR(13)

+ N’ IF EXISTS( SELECT name from sys.tables where name =”your table name”)   Select ”’+@DBNameVar+”’ as DBNAME, Count(*) from yourtablename

–EXEC sp_executesql @Statement — TO execute your statement

Print @Statement — To get print view of your sql command that get executed, your sql query can be anything you want, and you can put insert statement for that select command so all value gets into a table..

PRINT CHAR(13) + CHAR(13)

FETCH NEXT FROM AllDatabases INTO @DBNameVar

END

 

CLOSE AllDatabases

DEALLOCATE AllDatabases

 

 

Loop Through Each Table in the Database

–The query below can be used for getting the value from all the tables in each separate database, for example if you want to get count of a same table from all the data base or if you want to search for value from a table that exist in each database then you can use this query

 

DECLARE @Sql NVARCHAR(MAX) = NULL; –Declaring a variable to hold your sql query

SELECT @Sql = COALESCE(@Sql + ‘ UNION ALL ‘ + CHAR(13) + CHAR(10), ” ) + ‘SELECT * FROM ‘ + QUOTENAME([name]) + ‘[schemaname].[YourTableName]’
FROM master.sys.databases
WHERE NOT [name] IN ( ‘master’, ‘tempdb’, ‘model’, ‘msdb’ );

PRINT @Sql; –To get the print of the query

EXEC @Sql;  — to Execute the query

 

 

How to delete the duplicate record and get 1 unique record SQL

Scenario 
Capture25

We have dups in the table we need to get rid of dups and keep one original record

Solution :

WITH GetUniqueRecords
            AS (
    SELECT Name,
                Address,
                Ph.No,
                Row_number()
                OVER (
                    PARTITION BY Name, Address,Ph.No
            ORDER BY (
            SELECT 1) ) AS Rownum
        FROM   dbo.Employee)

DELETE FROM GetUniqueRecords WHERE Rownum>1

After running this query below
Select * from dbo.Employee 
You will get the unique records as all the dups will be deleted and one of the unique is kept//
Capture26