2

I am looking to run validation script across columns for all tables in a database in MS SQL SERVER . My query now is something like this:

CREATE PROCEDURE [dbo].[DDS1718_Validation] 
AS
BEGIN

SET FMTONLY OFF

DECLARE @ActualTableName AS NVarchar(255)

SELECT @ActualTableName =QUOTENAME(TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES

this however picks output for only one table. Thank you.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
D.Rane
  • 59
  • 9
  • 2
    why not select from INFORMATION_SCHEMA.COLUMNS and get all tables and all columns? what are you trying to do? – Jeremy Mar 19 '19 at 17:38
  • Are you looking for SELECT @ActualTableName = ISNULL(@ActualTableName, '') + QUOTENAME(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES ? – sacse Mar 19 '19 at 17:49
  • i am trying to run a validation script that validated all the columns in a table. Now i have multiple tables in a db . Instead of passing one table name at a time i want it to accept all the tablenames ,so that there is one run and validation is done across all Tables. – D.Rane Mar 19 '19 at 17:49
  • you can create a string with all table names with comma separator. – Hasan Mahmood Mar 19 '19 at 17:51
  • What input does your validation requires? What output do you need from the stored procedure? – Zohar Peled Mar 19 '19 at 17:52
  • The validation expects a table name at a time from param @ActualTableName. I expect the output of the SP to simply run all scripts for all tables at once instead of having me pass table name for each run. Hope this helps. – D.Rane Mar 19 '19 at 17:58
  • 1
    you can check out an undocumented SP: sp_MSforeachtable. article on usage: https://www.sqlshack.com/an-introduction-to-sp_msforeachtable-run-commands-iteratively-through-all-tables-in-a-database/ – Jeremy Mar 19 '19 at 18:05
  • So, what task are you running against each table? That's the real "meat" of the question here. If we know what you're doing against each one, we can help us built a dynamic statement that can be run against exist table in your database; if that is what you really need to do. – Thom A Mar 19 '19 at 18:09
  • I am running simple validations like isNumeric(COlumn name ), Year validation , date column validation etc. – D.Rane Mar 19 '19 at 18:12
  • 2
    Don't use `ISNUMERIC`, it returns false results; for example `ISNUMERIC('.')` returns `1` but will fail to convert, where as `ISNUMERIC('')` will return `0`, but *can* be converted. Use `TRY_CAST` or `TRY_CONVERT` – Thom A Mar 19 '19 at 18:18
  • This is way above your skill level - and I would challenge the idea that it makes any sense to "validate" your schema programatically. In addition, it seems like you want to "validate" the content of the rows/columns in your tables and not the definition of the tables and their columns. It certainly is not clear what you want to do. So I suggest you pick a table and write a script that does what you want for that table. Once you have that, it should provide a pattern for you to apply to all tables. – SMor Mar 19 '19 at 18:48

2 Answers2

0

You could use a table valued parameter. Before calling the Sp, populate the table valued parameter with all the table names and then call the sp and pass the parameter just like you would pass an int or a float parameter.

Here is more info : https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

J Sidhu
  • 677
  • 1
  • 4
  • 19
0
DECLARE @ActualTableName AS varchar(max)

SELECT @ActualTableName = COALESCE(@ActualTableName + ',', '') + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES

SELECT @ActualTableName

Now you have all table names, send this @ActualTableName in your stored procedure. In the stored procedure you can split this table names with ','.

split string in sql

Hasan Mahmood
  • 978
  • 7
  • 10